Microsoft Office Tutorials and References
In Depth Information
6. Include the Client Number, Client Name, and Billed fi elds in the design grid. Sort the records in
descending order by the Billed fi 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 fi 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
Technician-Client query.
8. Open the Technician-Client query in Design view and remove the Client table. Add the Hourly
Rate fi eld to the design grid following the Last Name fi eld. Calculate the number of hours each
technician has worked (YTD Earnings/Hourly Rate). Assign the alias Hours Worked to the
calculated fi eld. Change the caption for the Hourly Rate fi 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
Crosstab.
13. Submit the revised database in
the format specifi ed by your
instructor.
Figure 2–87
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
management.
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 fi 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 fi 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 fi elds for all
products where the Supplier Code is LG. Save the query as Lab 2-2 Step 2 Query.
Search JabSto ::




Custom Search