Microsoft Office Tutorials and References
In Depth Information
Extend Your Knowledge continued
Perform the following tasks:
1. Create the crosstab query shown in Figure 2–85 on the previous page. The crosstab groups average
of customers’ balances by city and groomer number.
2. Create a query to fi nd all customers who do not live in Grant City. Include the Customer Number,
Last Name, and Balance fi elds in the design grid. Save the query as Not Criteria Query.
3. Create a query to fi nd all customers who do not have a telephone number. Include the Customer
Number, Last Name, First Name, Street, and City fi elds in the query results. Save the query as
Missing Values Query.
4. Create a query to fi nd all customers whose balance is between $20.00 and $60.00. Include the
Customer Number, Last Name, and Balance fi elds in the design grid. Save the query as Number
Range Query.
5. Create a query to fi nd all customers where the groomer number is 203 or 205 and the balance
is greater than $40.00. Include the Customer Number, Last Name, First Name, Balance, and
Groomer Number fi elds in the design grid. Save the query as Compound Criteria Query.
6. Change the database properties, as specifi ed by your instructor. Submit the revised database in the
format specifi ed by your instructor.
Make It Right
Analyze a database and correct all errors and/or improve the design.
Correcting Errors in the Query Design
Instructions: Start Access. Open the Keep It Green database. See the inside back cover of this topic
for instructions for downloading the Data Files for Students, or see your instructor for information on
accessing the fi les required in this topic.
Keep It Green is a database maintained by a small landscaping business. The queries shown in
Figure 2–86 contain a number of errors that need to be corrected before the queries run properly.
The sort query shown in Figure 2–86a displays the query results in the proper order (First Name, Last
Name, Street, City) but it is sorted incorrectly. The query results should be sorted by last name within
city in ascending order. Also the caption for the Street fi eld should be Address. Save the query with
your changes.
When you try to run the join query for the Keep It Green database, the message shown in
Figure 2–86b appears. The query joins the Worker table and the Customer table. It also calculates the
total amount for each customer. The query should be sorted in alphabetical order by worker last name
and customer last name. Correct the error that is causing the message shown in Figure 2–86b and sort
the records properly. Save the query with your changes.
Change the database properties, as specifi ed by your instructor. Submit the revised database in
the format specifi ed by your instructor.
Search JabSto ::




Custom Search