Querying Your Database 1. Create a query called CustServerdAfter to show the customer information for all customers who received service after 12/15/2007. 2. Create a query called TechsHiredAfter that shows the technician name (first and last) and the technician number for those technicians hired after January 1, 2005. 3. Create a query called TotalNumPartsUsed that shows that the sum of the qty field in the service-parts-detail table. The field produced by this query should be called total number parts used. 4. Create a query called TechsDuring that shows the service call numbers for service calls during the weeks 12/4/16 through 12/18/16, inclusive in which technicians with tech number 4 or 17 were the technician. 5. Create a query GerrysCusts that shows all customer names serviced by the technician, Gerry Robinson. 6. Create a query called TotalPricePartsUsed that shows the total amount charged for parts for all of the service calls. You need to join service-parts-detail and parts tables and create a calculated query field that multiplies qty and price. The field produced by this query should be called the total parts price charged. 7. Create a query called TotalLaborChargePerCall that shows the price of labor charged for each call as a calculated field, along with the call-num from the service-labor-detail table. 8. Create a query called TechsHelpingCustomers that shows the technician names and customer names where the named technician had a call to help the named customer. Query 1 will find all customers who received service after 12/15/2007.

9. Create a query called PartsUsed that shows the technician name, part number, part description, and the number of that part used by the technician on any service call.

10. Create a query called PartMargin that shows the total difference for each customer (by name) between the cost of the parts used in the customer service and the price charged to the customer (the margin). Use the summation and group by feature to get a single margin total for each customer. Youll have to include the customer, service-call, service-parts-detail, and parts tables, although you will only display information from customer and parts.

Create a small database in Microsoft Access from scratch and how to extract information from that database. Youll submit your Microsoft Access file as your response to this lab. Designing queries to retrieved information from the database.

CUSTOMER Table:

		CUSTOMER
CUST-NUM	CUST-LAST	CUST-FIRST	CUST-ADDRESS	CUST-CITY	CUST-STATE
1456	Jones	Albert	43 Oak Drive	Denver	CO
1933	Valdez	Trini	1011 Hilltop Road	Charlotte	NC
2836	Gonzalez	Juan	678 8th Street	Seattle	WA
6321	Belli	Mary	101 Main Street	San Jacinto	CA
8760	Sansone	Jackie	9 Blueberry Court	Boston	MA

SERVICE-PARTS-DETAIL Table:

SERVICE-PARTS-DETAIL
CALL-NUM	PART-NUM	QTY
10796	DD-9192	4
10796	SS-1111	2
10797	AB-6784	6
10799	AB-6784	15

SERVICE-LABOR-DETAIL
CALL-NUM	LABOR-CODE	LABOR-HOURS
10796	A	1.50
10796	C	2.00
10798	B	5.00
10799	C	1.50
10799	D	1.50

SERVICE-LABOR-DETAIL Table:

TECHNICIAN Table:

TECHNICIAN

TECH-NUM

TECH-LAST

TECH-FIRST

HIRE-DATE

4

Robison

Gerry

6/1/1995

9

Whitfield

David

8/1/1997

17

Kaplan

Carl

1/1/1990

20

Chou

Leland

12/1/2000

		PARTS
PART-NUM	PART-DESC	COST	PRICE	QTY-ON-HAND
AB-6784	METER	$98.50	$125.00	33
DD-9192	DISC	$1.30	$1.69	220
SS-1024	COVER	$16.00	$19.79	78
SS-1099	EMBLEM	$38.00	$50.00	41
SS-1111	JUMPER	$12.50	$15.00
XC-3133	CABLE	$6.75	$7.99	77