 |
 |
 |
 |
 |
Access Project
Creating an Auto Repair Shop Business Database
Purpose: To demonstrate the ability to create a database, enter data and create reports.
Problem: An auto repair shop needs to maintain information on its jobs and customers. The shop specializes in repair jobs for local car dealers. The database it will use consists of two tables. The Jobs table contains data on jobs the repair shop has either started or has completed for each customer. The Customer table contains pertinent data about the auto repair shop’s customers.
Instructions: The structure for the Customer table is shown in figure 1 and the data is shown in figure 2. The structure for the Jobs table is shown in figure 3 and the data is shown in figure 4.
1. Create a new database on your storage device to store the data related to the auto repair shop. Call the database Repair Shop.
2. Create the Customer table using the structure shown in figure 1. Make the Customer ID the primary key. Use the name Customer for the table.
3. Add the data shown in Figure 2 to the Customer table.
4. Create the Jobs table using the structure shown in figure 3. Make the Job ID the primary key. Use the name Jobs for the table.
Optional: Instead of using a text field of size 1 for the Job Status field, use the yes/no data type instead. You will need to change the name of the field to something like “Job Completed” and explain in the description that the box will be checked when the job is completed.
Here’s another neat idea: Use the lookup Wizard to create a pull-down menu of the Customer IDs from the Customer table.
5. Create a form using all of the fields for the Jobs table.
6. Add the data shown in figure 4 to the Jobs table using the form.
7. Print both tables and put them in your portfolio.
Structure of the Customer table:
Field Name Data Type Field Size Primary Key? Description Customer ID Text 3 Yes Customer number (primary key) Customer Name Text 20 Customer/Dealer name Contact Person Text 20 Contact person Phone Text 8 Contact phone number
Figure 1
Data for the Customer table:
Customer ID Customer_Name Contact Person Phone COM Complete Auto Sales Your Name 455-3451 SPO Sports Car Emporium Mario Smellhouse 455-6639 PET Pettys Antiques Bonnie York 456-7877 TRA Travel All Auto Kari Thompson 415-2258 MEL Mels Classic Car Lot Mel Radbell 785-9111 OTT Ottos Old Autos Otto Foreman 555-8890
Figure 2
Structure of the Jobs table:
Field Name Data Type Field Size Primary Key? Description Job ID Text 4 Yes Job number (primary key) Customer ID Text 3 Customer ID number Job Description Text 25 General job description Job Status Text 1 Status of complete (C) or incomplete (I) Completion Date Date/Time Date job is to be completed Quote Currency Customer’s cost for job
Figure 3
Data for the Jobs table:
Job ID Customer ID Job Description Job Status Completion Date Quote A234 COM Your Name I 6/30/98 1200 P435 SPO Rebuild Tran – 63 Merc C 5/12/98 1685 A342 SPO Bumper Repair – 53 Nash I 9/21/98 300 A564 PET Valve Job – 66 Falcon C 4/15/98 600 P125 TRA Brake Drums – 59 Linc I 6/15/98 1400 P854 MEL Re-paint – 29 Ford C 3/1/98 957 A585 PET Door Panels – 61 Falcon C 3/27/98 200 A448 COM Heater Repair – 53 Ford I 5/6/98 360 A687 MEL Repair Windshield – 56 PU I 7/22/98 300 P658 OTT Floor Boards – 61 Chevy I 9/30/98 1500
Figure 4
Reports:
Report #1: Create a report with the following fields:
- Job Description
- Job status
- Completion Date
- Quote
Title the report “Job Summary Report”.
Go to design view and add your name to the page footer. Print the report and put it in your portfolio.
Report #2:Create a report with the following fields:
- Customer Name (from Customer Table)
- Job Description (from Jobs Table)
- Quote (from Jobs Table)
You will need to create a Relationship between the two tables first.
Title the report “Customer Quotes”.
Report #3: Create a query that prints out the Job Description, Completion date and Customer name for jobs over $1000. You will need to create a relationship between the two tables in order to do this. (Note: You need to use the quote field in the query, but you don’t want the quote field to show up on the query results).
Create a report from the query. Title the report “Jobs Over $1000”. Go to design view and add your name to the page footer of the report. Print the report and put it in your portfolio.