Microsoft Access 2007 Chapter 2 – Lab Test B

Querying the Fritz’s Grooming Database

Purpose:  To demonstrate the ability to create and use queries.

Problem:  You have been asked by the Manager of Fritz’s Grooming to collect some facts to assist in the planning for the next six months. The director has given you a list of items outlining the type of information needed. You must supply the correct information.

Instructions:  Use the database created in Access Chapter 1 – Lab Test B, Fritz’s Grooming, or a copy of this database supplied by your instructor. Create queries to provide the following information from the Members table.

  1. A query showing the Pet ID, Breed, and Dog Name sorted by Dog Name within Breed saved as step 1.
  2. A query showing the average age of the pets in all breeds saved as step 2.
  3. A query showing the lowest age listed for a breed saved as step 3.
  4. A query with Pet ID, Owner Name, Dog Name, Breed, and Groomer ID of Pets where the Groomer ID begins with a 4 or 6 saved as step 4.
  5. A query of pets with their associated groomers. The list should show the Station Number, Full Name, and Dog Name, and should be in ascending order by Dog Name within Station saved as step 5.
  6. Using both tables, a query that includes the Groomer ID, Full Name, Station Number, Dog Name, and Base Rate of all groomers that have a base rate greater than $40 and have a Station Number of 1 saved as step 6.
  7. A query showing all pets along with their base rate for grooming and calculated 15% extra charge for pet accessories. List the Pet ID, Owner Name, Dog Name, and Base Rate along with the computed field Accessories. Format the dues using dollar signs and two decimal positions saved as step 7.
  8. A query count of the pets, grouped by breed saved as step 8.
  9. Using both tables, a query count of the pets, grouped by groomer’s full name. Show only the top two groomers saved as step 9.
  10. A Crosstab query of groomers per breed showing a count of the number of pets saved as step 10.
  11. A query list of pets for each station. Use a parameter to select the station saved as step 11.