To complete the assignment, follow the instructions below.
Use the auto repair shop database found within this zip file. Open the Jobs table in Design View and execute the database maintenance tasks described below.
Change the field width of Job Description to 30.
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.
Change the Job Status field to a lookup field.
In datasheet view: Change the Job Description for Job ID A564 from Valve Job – 66 Falcon to Rebuild Trans – 61 Thunderbird.
Add Totals to the Job datasheet to show average quote.
Create and save the following validation rules for the Jobs table:
Assign a default value of I to the Job Status field.
Make Customer ID a required field.
Specify that the values for Quote must be greater than or equal to 100 and less than or equal to 10000.
Create a form for the Jobs table. Save this form as Job Form.
Using Form view, add the following record to the end of the Jobs table: P654, TRA, Master Cyl. Rebuild – 54 Merc, I, 12/1/2001, 10/15/2001, 750.
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.
From the Database window, copy the Jobs Table, then past a copy of it. Name it Jobs2.
Using a query for the Jobs2 table, delete all records with a Job Status of C and a Completion Date before April 1, 2001. Save the query as Q11.
Specify referential integrity for Customer ID between the Customer table (the “one” table) and the Jobs table (the “many” table).
