Microsoft Access 2010 Chapter 3 – Lab Test B

Maintaining the Fritz’s Grooming Database

Purpose: To demonstrate the ability to maintain a database.

Problem: The boss has supplied you with information that requires changes to the Fritz’s Grooming database.

Instructions: Use the Fritz’s Grooming database created in Access Chapter 1 – Lab Test A for this assignment or a copy of the database supplied by your instructor. Execute the following tasks and print the results.

  1. Open the Fritz’s Grooming database and then open the Pet table in Design view.
  2. Add a multi-value Lookup field, Services, to the Pet table. The field should appear after the Breed field. The field will contain data on the grooming services normally requested. The services are CLP (hair clipping), NT (Nail Trim), FB (Flea Bath), and NB (Normal Bath). Save these changes to the structure.
  3. Change all the entries in the Services column to CLP.
  4. Open the Pet table and make the following changes. You can use either the Find button or Filter By Selection to locate the records to change:
    1. Change the services for pets 2379 and 4895 to NB and NT.
    2. Add services for pets 2746 and 8842 of NB and NT.
    3. Change the name of pet 9685 to Adrian.
  5. Change the alternate background color on the datasheet to Orange, Accent 6, Lighter 60% and remove the horizontal gridlines. Save the changes to the layout of the table.
  6. Create the following validation rules for the Pet table and save the changes.
    1. Specify the legal values >0 and < 30 for the Age field. Include validation text.
    2. Specify the legal values M and F for the Gender field. Include validation text.
    3. Make Owner Name a required field.
  7. Open the Pet table and use Filter By Form to find all records where the pet ID is 9082 and the Dog name is Apollo.
  8. An owner has brought in their pet to be groomed. You assign the pet an id of 4012. The owner’s name is Kennedy and the dog’s name is Chewie. He is a male Morkie dog and will need clipping and nail trimming. He is 2 years old. Groomer 345879 will be taking care of this new pet. Create a split form for the Pet table and use this split form to add the record. Save the split form as Pet Split Form.
  9. Specify referential integrity between the Pet table (the one table) and the Groomer table (the many table). Cascade the update but not the delete.
  10. Add the Services field to the Pet Report. The field should follow the Dog ID field. Save the changes to the report.
  11. Open the Groomer table. Groomers must now pay a portion of their base price to the store as a station fee. Each groomer must pay 10% of their base price the store. Add a calculated field called Fee to the Groomer table after the Base Rate to calculate this. Format the field as currency. Save the changes to the structure.
  12. Submit the revised database in the format specified by your instructor.