Microsoft Access 2007 Chapter 1 – Review for Test – Test A

Creating an Auto Repair Shop Business Database

Purpose: To demonstrate the ability to create a database, create a form, 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 either has 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.

1. Create a new database 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 and print it.

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 and print it.

8. Create a split form for the Customer table. Save the form using the name, Customer.

9. Create and print a report using the Jobs table that lists the Job ID, Job Description, Completion Date, and Quote. Use the Flow style. Title the report Job Summary Report. Print the report and save it. Your report should display similar to the one shown in Figure A1A – 1.

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
M345	JMS	Grill Work – 67 Chevy	I	6/30/2005	2200
M653	KSI	Rebuild Tran – 73 Merc	C	5/12/2005	2685
B423	KSI	Bumper Repair – 55 Nash	I	9/21/2005	400
B746	ROM	Valve Job – 68 Falcon	C	4/15/2005	700
M236	TRW	Brake Drums – 69 Linc	I	6/15/2005	2400
M965	JTC	Re-paint – 39 Ford	C	3/1/2005	1057
B474	ROM	Door Panels – 71 Falcon	C	3/27/2005	300
B559	JMS	Heater Repair – 63 Ford	I	5/6/2005	460
B365	JTC	Windshield Repair – 66 PU	I	7/22/2005	400
M547	NQR	Floor Boards – 71 Chevy	I	9/30/2005	2500
B147	NQR	Muffler – 75 LTD	I	6/26/2005	250
B338	JMS	Tie-rods – 78 GTO	C	4/17/2005	510
M482	KSI	Shocks – 78 Pontiac	C	7/21/2005	660
B628	JTC	Gas Tank – 75 Pinto	I	8/16/2005	420
B553	ROM	Trunk Deck – 65 Galaxy	C	9/4/2005	1080
M182	JTC	Overhaul – 83 LTD	C	8/12/2005	990
B527	JMS	Brake Cyl. – 80 Fairlane	I	9/21/2005	890
M982	NQR	Muffler – 72 Corvette	I	10/12/2005	580
M522	JTC	Tune-up – 71 Lincoln	C	4/30/2005	490
B932	JMS	Manifold – 37 P-Arrow	I	10/16/2005	385

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	25		Customer/Dealer name
Contact Person	Text	20		Contact person
Telephone	Text	8		Contact telephone number

Table A1A – 3

u Data for the Customer table

Customer ID	Customer_Name	Contact Person	Telephone
JMS	Jacob’s Motor Sales	Jacob Daniels	555-3451
KSI	Keith’s Sports Cars, Inc.	Keith Moorehouse	555-6639
ROM	Randy’s Old Makes	Randy Petersen	555-7877
TRW	Trevor’s Rolling Wheels	Trevor Craig	555-2258
JTC	Jack’s Timeless Classics	Jack Johnson	555-9111
NQR	Nick’s Quick Rides	Nick Stone	555-8890

Table A1A – 4

Figure A1A – 1