Microsoft Access 2010 Chapter 3 – Lab Test A

Maintaining the HaveMore Electronics Database

Purpose: To demonstrate the ability to maintain a database.

Problem: The boss has supplied you with information that requires changes to the HaveMore Electronics database.

Instructions: Use the HaveMoreElectronics 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 HaveMore Electronics database and then open the Sales Rep table in Design view.
  2. Add a Lookup field, JobType, to the Sales Rep table. The field should appear after the Sales Rep ID field. The field will contain data on the job classification for a sales representative. The job types are PT (Part Time), FT (Full Time), and ST (Seasonal Time). Save these changes to the structure.
  3. Using a query, change all the entries in the Job Type column to PT. Save the query as Job Type Update Query.
  4. Open the Sales Rep 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 Job type for sales representatives125, 465, 512, 678 to FT.
    2. Change the Job type for sales representatives101 and 287 to ST.
    3. Change the name of sales representative 475 to Leon, Tommy.
  5. Change the alternate background color on the datasheet to white and remove the vertical gridlines. Save the changes to the layout of the table.
  6. Create the following validation rules for the Sales Rep table and save the changes.
    1. Specify the legal values PT, FT, and ST for the Job Type field. Include validation text.
    2. Assign a default value of PT to the Job Type field.
    3. Specify the legal values M and F for the Gender field. Include validation text.
    4. Make Full Name a required field.
  7. Open the Sales Rep table and use Filter By Form to find all records where the sales representative is located in HME 15 and has a Job type of PT, and then delete these records.
  8. Store HME 17 has hired a new sales representative and needs to add the record to the Sales Rep table. The new employee is Mike Smith. He is 35, and is hired today with a Sales Rep ID of 651. His quota is set initially to 1,500,000. To date, Mike has had no sales yet. Create a split form for the Sales Rep table and use this split form to add the record. Save the split form as Sales Rep Split Form.
  9. Specify referential integrity between the Outlets table (the one table) and the Sales Rep table (the many table). Cascade the update but not the delete.
  10. Add the Job Type field to the Sales Rep Report. The field should follow the Sales Rep ID field. Save the changes to the report.
  11. Submit the revised database in the format specified by your instructor