K212 - Access Project 2, Part 2

Querying the Repair Shop Database

Problem: Query an auto repair shop database in a variety of ways.

Instructions: Use the auto repair shop database you created in class (Project 1) for this assignment. Execute the following tasks on the computer.

Join the Jobs and Customer tables. Using the fields Job ID, Customer ID, Job Status, Customer Name, and Phone, and display all records.

Restrict the records retrieved in the above task to only jobs with a Job Status of I. Display the results. Print.

From the Jobs table, display the highest Quote with a Job Status of I. Write this number down on the earlier print-out.

Display the following records from the Jobs and Customer tables: Job ID, Job Status, Quote, Customer Name. Sort by Job Status and then within it by Quote (ascending). Save this query as Query 1.

Create a query to find average Quote by Job Status. Print.

From the Jobs table, display the average Quote by Customer ID. Save this query as Average Quote by Customer

If each customer is required to make a 10% deposit for each job, display the Job ID, Customer ID, and deposit amount for every record in the Jobs table. [Hint: Create a new field called Deposit. The appropriate formula is Deposit:0.1*[Quote] Use Properties within the Shortcut menu to format deposits with a dollar sign and two decimal positions.] Print.

Close the Repair Shop database and exit Access