Microsoft Office Tutorials and References
In Depth Information
10. Create a new query based on the Recruiter table. Use the Min, Max, and Avg aggre-
gate functions to find the lowest, highest, and average values in the Salary field.
Name the three aggregate fields Lowest Salary, Highest Salary, and Average Salary,
respectively. Save the query as SalaryStatistics , and then run the query. Resize all
columns in the datasheet to fit the data, print the query datasheet, and then save and
close the query.
11. Open the SalaryStatistics query in Design view. Modify the query so that the records
are grouped by the BonusQuota field. Save the query as
SalaryStatisticsByBonusQuota , run the query, print the query datasheet, and then
close the query.
12. Close the Recruits database.
Case Problem 1
Data File needed for this Case Problem: Videos.mdb ( cont. from Tutorial 2 )
Lim’s Video Photography Youngho Lim wants to view specific information about his
clients and video shoot events. He asks you to query the Videos database by completing
Using what you learned
in the tutorial, create
queries to retrieve data
about video photogra-
1. Open the Videos database located in the Brief\Case1 folder provided with your
2. Define the necessary one-to-many relationships between the database tables, as fol-
lows: between the primary Client table and the related Contract table, between the
primary Contract table and the related Shoot table, and between the primary
ShootDesc table and the related Shoot table. ( Hint : Add all four tables to the
Relationships window, and then define the three relationships.) Resize the field lists,
as necessary, to display all the field names. Select the referential integrity option and
the cascade updates option for each relationship.
3. Create a select query based on the Client and Contract tables. Display the
ClientName, City, ContractDate, and ContractAmt fields, in that order. Sort in ascend-
ing order based on the ClientName field values. Run the query, save the query as
ClientContracts , and then print the datasheet.
4. Use Filter By Selection to display only those records with a City field value of
Oakland in the ClientContracts datasheet. Print the datasheet and then remove the
filter. Save and close the query.
5. Open the ClientContracts query in Design view. Modify the query to display only
those records with a ContractAmt value greater than or equal to 600. Run the query,
save the query as ContractAmounts , and then print the datasheet.
6. Switch to Design view for the ContractAmounts query. Modify the query to display
only those records with a ContractAmt value greater than or equal to 600 and with a
City value of San Francisco. Also modify the query so that the City field values are not
displayed in the query results. Run the query, save it as SFContractAmounts , print the
datasheet, and then close the query.
7. Close the Videos database.