K212- Access Project 3

Maintaining the Repair Shop Database

Purpose: To demonstrate the ability to maintain a database.

Problem: You have been supplied with information that requires you to make some changes to the Repair Shop database.

Instructions: Use the Repair Shop database created in Access Project 1 for this assignment. Execute the following tasks.

1. Open the Repair Shop database and then open the Jobs table.

2. Order the records in the Jobs table in ascending sequence by Job Description

3. Adjust all columns to the best size. Use Page Setup in the File menu to set all margins to 0.5 inches.

4. Display the Design window for Jobs table and change the field width of Job Description to 30.

5. Add a field called Quote Date between the Completion Date and Quote fields. Define the field as Date/Time. This field will contain the date the quoted price was given.

6. Save these changes and display the Jobs table in Datasheet view.

7. Change the Job Description for Job ID A564 from Valve Job – 66 Falcon to Rebuild Trans – 61 Thunderbird. Resize the Job Description column to the best size.

8. Order the records by Customer ID.

9. If necessary, again adjust all columns to the best size and use Page Setup in the File menu to set all margins to 0.5 inches and then print the table.

10. Create and save the following validation rules for the Jobs table:

a. Assign a default value of I to the Job Status field.

b. Make Customer ID a required field.

c. Specify that the values for Quote must be greater than or equal to 100 and less than or equal to 10000.

11. Use AutoForm to create a form for the Jobs table. Save this form as Job Form.

12. Using Form view, add the following record to the end of the Jobs table:

P654, TRA, Master Cyl. Rebuild – 54 Merc, I, 12/1/98, 10/15/98, 750

13. Use the Find button while in form view to locate the jobs with Job IDs of A342 and P658. Change the I to C in the Job Status fields for both records.

14. Change to Datasheet view and adjust all columns to the best size. Use Page Setup in the File menu to set all margins to 0.5 inches and then print the table.

15. Create a Delete query for the Jobs table and delete all records with a Job Status of C and a Completion Date before April 1, 1998.

16. Close the query without saving it.

17. Print the Jobs table once more.

18. Specify referential integrity for Customer ID between the Customer table (the “one” table) and the Jobs table (the “many” table).

19. Close the database and exit Access.