Matthew Castillo
Syllabus Access Excel 1 Excel 2 Visual Basic
BU 4230 Information Systems for Managers
My Access project is going to be used for a Mechanic shop. It will contain a Customers table, Mechanics table, and Repair Order table.
| VIN # | Last name | First name | Address | Phone # | Birthdate | Car make | Car model | Car year |
|---|---|---|---|---|---|---|---|---|
| CHE207899 | Castillo | Edward | 481 Kirkman Ave. | (516) 354-4053 | 6/20/1954 | Chevy | Malibu | 1987 |
| CRY207899 | Castillo | Edward | 481 Kirkman Ave. | (516) 354-4053 | 6/20/1954 | Crysler | LaBaron | 1995 |
| FOR097899 | McLean | Barbara | 474 Keller Ave. | (516) 328-2821 | 11/9/1946 | Ford | Grand Marquis | 2001 |
| FOR217899 | McLean | Kenny | 1323 Sun Ave. | (516) 775-2309 | 10/21/1963 | Ford | Explorer | 2006 |
| GMC207899 | Castillo | Matthew | 481 Kirkman Ave. | (516) 354-4053 | 11/20/1976 | GMC | Yukon | 1993 |
| HON157899 | Bucicchia | Christopher | 35 Meacham Ave. | (516) 840-1878 | 7/15/1976 | Honda | Accord | 2001 |
| HON307899 | Watras | Magdalena | 59 Rumford Rd. | (347) 572-5884 | 5/30/1980 | Honda | Civic | 1996 |
| NIS097899 | LaBrandi | Samantha | 35 Meacham Ave. | (516) 840-1878 | 1/9/1980 | Nissan | Maxim | 2000 |
| VOL057899 | Castillo | Adele | 481 Kirkman Ave. | (516) 354-4053 | 3/5/1955 | Volvo | G93 | 1994 |
| Mechanic # | First Name | Last Name | Specialty | Certified | Hourly wages |
|---|---|---|---|---|---|
| EGC04 | Edward | Castle | A technician | Yes | $29.50 |
| JJA06 | John | Anthony | Brake specialist | Yes | $22.35 |
| MRC01 | Matthew | Connery | A technician | Yes | $37.00 |
| SMD09 | Sara | Dansbury | Minor repairs | No | $18.00 |
| TDM02 | Timothy | Marian | A technician | Yes | $28.75 |
| Repair order # | VIN # | Mechanic # | Repair description | Labor hours | Labor cost | Parts cost | Total price | Completion date | Paid amount | Balance due |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | CHE207899 | EGC04 | Exhaust | 2 | $170.00 | $223.85 | $393.85 | 10/14/2006 | $220.00 | $173.85 |
| 2 | CRY207899 | EGC04 | Rear brakes | 1.5 | $127.50 | $72.88 | $200.38 | 10/9/2006 | $200.38 | $0.00 |
| 3 | CRY207899 | EGC04 | Shocks | 3 | $255.00 | $320.00 | $575.00 | 9/28/2006 | $510.00 | $65.00 |
| 4 | FOR097899 | SMD09 | Oil change | .2 | $17.00 | $15.50 | $32.50 | 9/28/2006 | $32.50 | $0.00 |
| 5 | FOR217899 | TDM02 | Tune up | 2 | $170.00 | $382.30 | $552.30 | 3/23/2005 | $400.00 | $152.30 |
| 6 | VOL057899 | MRC01 | Tires | 2.2 | $187.00 | $440.00 | $627.00 | 3/23/2005 | $440.00 | $187.00 |
| 7 | VOL057899 | MRC01 | Exhaust | 2 | $170.00 | $185.00 | $355.00 | 8/19/2006 | $325.00 | $30.00 |
| 8 | NIS097899 | JJA06 | Front brakes | 1.3 | $110.50 | $79.95 | $190.45 | 8/19/2006 | $150.00 | $40.45 |
| 9 | GMC207899 | JJA06 | Oil change | .2 | $17.00 | $13.00 | $30.00 | 8/19/2006 | $30.00 | $0.00 |
| 10 | GMC207899 | JJA06 | Cut rotors | .6 | $51.00 | $0.00 | $51.00 | 11/20/2006 | $25.00 | $26.00 |
| 11 | HON307899 | SMD09 | Oil change | .2 | $17.00 | $22.65 | $39.65 | 11/28/2006 | $30.00 | $9.65 |
| 12 | HON157899 | MRC01 | Oil change | .2 | $17.00 | $14.00 | $21.00 | 11/28/2006 | $21.00 | $0.00 |
| 13 | HON157899 | MRC01 | Tune up | 3.5 | $297.50 | $495.80 | $793.30 | 12/2/2006 | $300.00 | $493.30 |
My database will contain queries, the first below is to pull up the customer's that have have open balances.
| Last name | First name | Address | Car model | Car year | Repair description | Total price | Balance due | Completion date |
|---|---|---|---|---|---|---|---|---|
| Castillo | Edward | 481 Kirkman Ave. | Malibu | 1987 | Exhaust | $393.85 | $173.85 | 10/14/2006 |
| Castillo | Edward | 481 Kirkman Ave. | LaBaron | 1995 | Shocks | $575.00 | $65.00 | 9/28/2006 |
| McLean | Kenny | 1323 Sun Ave. | Explorer | 2006 | Tune up | $552.30 | $152.30 | 3/23/2005 |
| Castillo | Adele | 481 Kirkman Ave. | G93 | 1994 | Tires | $627.00 | $187.00 | 3/23/2005 |
| Castillo | Adele | 481 Kirkman Ave. | G93 | 1994 | Exhaust | $355.00 | $30.00 | 8/19/2006 |
| LaBrandi | Samantha | 35 Meacham Ave. | Maxim | 2000 | Front brakes | $190.45 | $40.45 | 8/19/2006 |
| Castillo | Matthew | 481 Kirkman Ave. | Yukon | 1993 | Cut rotors | $51.00 | $26.00 | 11/20/2006 |
| Watras | Magdalena | 59 Rumford Rd. | Civic | 1996 | Oil change | $39.65 | $9.65 | 11/28/2006 |
| Bucicchia | Christopher | 35 Meacham Ave. | Accord | 2001 | Tune up | $793.30 | $493.30 | 12/2/2006 |
The SQL for my first query follows:
SELECT Customers.[Last name], Customers.[First name], Customers.Address,
Customers.[Car model], Customers.[Car year], Repairs.[Repair description],
Repairs.[Total price], Repairs.[Balance due], Repairs.[Completion date]
FROM Customers INNER JOIN Repairs ON Customers.[VIN #]=Repairs.[VIN #]
WHERE (((Repairs.[Balance due])>0));
My next query is to calculate the mechanics total wages earned per job.
| Mechanic # | Last Name | Hourly wages | Repair order # | Labor hours | Wages earned |
|---|---|---|---|---|---|
| EGC04 | Castle | $29.50 | 1 | 2 | $59.00 |
| EGC04 | Castle | $29.50 | 2 | 1.5 | $44.25 |
| EGC04 | Castle | $29.50 | 3 | 3 | $88.50 |
| JJA06 | Anthony | $22.35 | 8 | 1.3 | $29.06 |
| JJA06 | Anthony | $22.35 | 9 | .2 | $4.47 |
| JJA06 | Anthony | $22.35 | 10 | .6 | $13.41 |
| MRC01 | Connery | $37.00 | 6 | 2.2 | $81.40 |
| MRC01 | Connery | $37.00 | 7 | 2 | $74.00 |
| MRC01 | Connery | $37.00 | 12 | .2 | $7.40 |
| MRC01 | Connery | $37.00 | 13 | 3.5 | $129.50 |
| SMD09 | Dansbury | $18.00 | 4 | .2 | $3.60 |
| SMD09 | Dansbury | $18.00 | 11 | .2 | $3.60 |
| TDM02 | Marian | $28.75 | 5 | 2 | $57.50 |
The SQL for my second query is:
SELECT Mechanics.[Mechanic #], Mechanics.[Last Name], Mechanics.[Hourly
wages], Repairs.[Repair order #], Repairs.[Labor hours], [Hourly wages]*[Labor
hours] AS Expr1
FROM Mechanics INNER JOIN Repairs ON Mechanics.[Mechanic #]=Repairs.[Mechanic #]
GROUP BY Mechanics.[Mechanic #], Mechanics.[Last Name], Mechanics.[Hourly
wages], Repairs.[Repair order #], Repairs.[Labor hours], [Hourly wages]*[Labor
hours];
The next query I have for this database is below:
| Repair order # | Mechanic # | Repair description | Total price | Paid amount | Balance due |
|---|---|---|---|---|---|
| 1 | EGC04 | Exhaust | $393.85 | $220.00 | $173.85 |
| 3 | EGC04 | Shocks | $575.00 | $510.00 | $65.00 |
| 5 | TDM02 | Tune up | $552.30 | $400.00 | $152.30 |
| 6 | MRC01 | Tires | $627.00 | $440.00 | $187.00 |
| 7 | MRC01 | Exhaust | $355.00 | $325.00 | $30.00 |
| 8 | JJA06 | Front brakes | $190.45 | $150.00 | $40.45 |
| 10 | JJA06 | Cut rotors | $51.00 | $25.00 | $26.00 |
| 11 | SMD09 | Oil change | $39.65 | $30.00 | $9.65 |
| 13 | MRC01 | Tune up | $793.30 | $300.00 | $493.30 |
The SQL is:
SELECT Repairs.[Repair order #], Repairs.[Mechanic #], Repairs.[Repair
description], Repairs.[Total price], Repairs.[Paid amount], Repairs.[Balance
due]
FROM Repairs
WHERE (((Repairs.[Balance due])>0));