Microsoft Office Tutorials and References
In Depth Information
6. Include the Client Number, Client Name, and Billed ﬁ elds in the design grid. Sort the records in
descending order by the Billed ﬁ eld. Display only the top 25 percent of the records in the query
result. Save the query as Lab 2-1 Step 6 Query.
7. Join the Technician and the Client table. Include the Technician Number, First Name, and Last
Name ﬁ elds from the Technician table. Include the Client Number, Client Name, and Billed from
the Client table. Sort the records in ascending order by technician’s last name and client name.
All technicians should appear in the result even if they currently have no clients. Save the query as
8. Open the Technician-Client query in Design view and remove the Client table. Add the Hourly
Rate ﬁ eld to the design grid following the Last Name ﬁ eld. Calculate the number of hours each
technician has worked (YTD Earnings/Hourly Rate). Assign the alias Hours Worked to the
calculated ﬁ eld. Change the caption for the Hourly Rate ﬁ eld to Rate. Display hours worked as an
integer (0 decimal places). Use the Save As command to save the query as Lab 2-1 Step 8 Query.
9. Create a query to display the average billed amount for all clients. Save the query as Lab 2-1
Step 9 Query.
10. Create a query to count the number of clients for technician 23. Save the query as Lab 2-1
Step 10 Query.
11. Create a query to display the average billed amount for each technician. Save the query as Lab 2-1
Step 11 Query.
12. Create the crosstab shown in Figure 2–87. The crosstab groups total of clients’ paid amounts by
city and technician number. Save
the crosstab as City-Technician
13. Submit the revised database in
the format speciﬁ ed by your
In the Lab
Lab 2: Querying the Hockey Fan Zone Database
Problem: The management of the Hockey Fan Zone store has determined a number of questions it
wants the database management system to answer. You must obtain answers to the questions posed by
Instructions: Use the database created in the In the Lab 2 of Chapter 1 on page AC 68 for this
assignment, or see your instructor for information on accessing the ﬁ les required for this topic.
Perform the following tasks:
1. Open the Hockey Fan Zone database and use the query wizard to create a query that includes the
Item Number, Description, On Hand, and Cost ﬁ elds for all records in the Item table. Name the
query Lab 2-2 Step 1 Query.
2. Create a query that includes the Item Number, Description, Cost, and Supplier Code ﬁ elds for all
products where the Supplier Code is LG. Save the query as Lab 2-2 Step 2 Query.