Project 1

Access Project 1

Creating an Auto Repair Shop Business Database

Purpose: To demonstrate the ability to create a database, use form view, and create a report.

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 Jobs table is shown in table A1A-1 and the data is shown in table A1A-2. The structure for the Customer table is shown in table A1A-3 and the data is shown in table A1A-4.

u 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

Table A1A - 1

u Data for the Jobs table

Job ID	Customer ID	Job Description	Job Status	Completion Date	Quote
P234	COM	Grill Work – 57 Chevy	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
A369	OTT	Muffler – 65 LTD	I	6/26/98	150
A227	COM	Tie Rods – 68 GTO	C	4/17/98	410
P593	SPO	Shocks – 68 Pontiac	C	7/21/98	560
A309	MEL	Gas Tank – 74 Pinto	I	8/16/98	320
A661	PET	Trunk Deck – 61 Galaxy	C	9/4/98	980
P293	MEL	Overhaul – 73 LTD	C	8/12/98	890
A419	COM	Brake Cyl. – 70 Fairlane	I	9/21/98	790
P871	OTT	Muffler – 62 Corvette	I	10/12/98	480
P411	MEL	Tune Up – 61 Lincoln	C	4/30/98	390
A821	COM	Manifold – 27 P-Arrow	I	10/16/98	285

Table A1A - 2

u 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

Table A1A - 3

u Data for the Customer table

Customer ID	Customer_Name	Contact Person	Phone
COM	Complete Auto Sales	Dave Lawrence	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

Table A1A - 4

1. Create a new database on your floppy diskette to store the two tables related to the auto repair shop. Call the database Repair Shop.

2. Create the Jobs table using the structure shown in Table A1A-1. Make the Job ID the primary key, no duplicates. Use the name Jobs for the table.

3. Add the data shown in Table A1A-2 to the Jobs table.

4. Save the Jobs table.

5. Create the Customer table using the structure shown in Table A1A-3. Make the Customer ID the primary key, no duplicates. Use the name Customer for the table.

6. Add the data shown in Table A1A-4.

7. Save the Customer table.

8. Create a form for the Customer table. While in form view, click on the print icon to print a form view listing of the Customer Table. Save the form using the name Customer.

9. Using Report Wizard, create a report using the Jobs table listing the Job ID, Job Description, Completion Date, and Quoted price. Title the report Job Summary Report. Print the report and save it. Your report should appear similar to that shown in Figure A1A-1.

10. Close the database and exit Access.