Access Database Assignment
The assignment is worth 25 points
 
a) Using MS Access, create each of the five tables given below.  Which tables represent Master tables?  Transaction tables?  For the master tables identify which attributes are reference fields and summary fields.
b) Print the relationships from your database, highlighting the primary keys (you can do this by using printscreen if you'd like)
c) Display the name and address for each customer's sales representative.  That is, create a query which combines the SLSREP and the CUSTOMER tables, and reports CUSTNUM, CUSTNAME, SLSRNAME and SLSRADDR information. 
d) Construct another query which reports the same information as in part c), but now only for sales representative 3.
e) Construct a query which reports the same information as in part d), but now only for those customers who have beginning balance over $500.
f) Construct a query which shows exactly what each customer has ordered. The information should include customer number, name, description of each item, quantity and price of each item and the extended price.
g) Design a query which shows the STATUS of each customer in the CUSTOMER table that has open orders in the transactions file. The information should include each of these customer’s number, name, address, beginning balance, total balance and credit limit.STATUS should be "OVER" if a customer's total balance exceeds his/her credit limit.  Otherwise, STATUS is "OK.
h) Design a query which calculates the total commission for each sales rep.  Show each sales reps name, their commission based on open orders (only) and then the updated total commission.  Note, you don't have to change the value for TOTCOMM in the table, just report what it will eventually be updated to.
i) Design a query that shows which products should be reordered based on the REORDQ, the quantity on hand and the new orders.  This query should show the each part's number, description, initial quantity on hand, the quantity ordered in open orders, the new quantity on hand, the reorder quanitty, and whether the part should be re-ordered or not.
j) Design a query that you think is interesting and useful.  Be sure to explain to me why it would be helpful for this business!

This assignment is due at the beginning of class on May 3 and is to completed in groups of up to 3 (not 4, not 5 ...)

E-mail me a copy of your database

Turn in paper copies of:

DATA

CUSTOMER
CUSTNUM
 CUSTNAME CUSTADDR
BEGBAL
CREDLIM
SLSRNUM
124
 ADAMS, SALLY 481 OAK, LANSING, MI
418.75
500
3
256
 SAMUELS, ANN 215 PETE, GRANT, MI
10.75
800
6
311
 DANIELS, TOM 914 CHERRY,KENT, MI
320.75
300
6
366
 ADAMS, SALLY 16 ELM, LANSING, MI
908.75
1000
3
405
 WILLIAMS, AL 519 WATSON, GRANT, MI
201.75
800
12
412
 ROBERTS, JUDY 481 OAK,LANSING, MI
215.25
500
6
522
 NELSON, MARY 108 PINE, ADA, MI
49.50
800
12
567
 BAKER, JOE 808 RIDGE, HARPER, MI
201.20
300
6
622
 MARTIN,DAN 419 CHIP, GRANT, MI
575.50
500
3

ORDER
ORDNUM
ORDDATE
CUSTNUM
12489
3/14/02
124
12490
3/14/02
315
12491
3/16/02
412
12492
3/16/02
256
12493
3/17/02
522
12494
3/20/02
124
12495
3/20/02
522

PART
PARTNUMB
PARTDESC
QONHAND
 REORDQ CLASS
WRHSNUM
UNITPRCE
AX12 IRON
104
 80 HW
3
17.95
AZ52 SKATES
20
 10 SG
2
24.95
BA74 BASEBALL
40
 20 SG
1
4.95
BH22 TOASTER
95
 50 HW
3
34.95
BT04 STOVE
11
 10 AP
2
402.99
BZ66 WASHER
52
 40 AP
3
311.95
CA14 SKILLET
2
 2 HW
3
19.95
CB03 BIKE
44
 40 SG
1
187.50
CX11 MIXER
112
 50 HW
3
57.95
CZ81 WEIGHTS
208
 50 SG
2
108.99

PARTORD
ORDNUM
PARTNUM
QUANTITY
QUOTPRCE
12489
AX12
11
14.95
12490
BT04
1
402.99
12490
BZ66
1
311.95
12491
CB03
4
175.00
12492
CX11
2
22.95
12493
AZ52
2
22.95
12493
BA74
4
4.95
12493
CB03
2
175.00
12494
BT04
1
402.99
12495
CZ81
2
108.99

SLSREP
SLSRNUM
 SLSRNAME SLSRADDR
TOTCOMM
COMMRATE
3
 JONES, MARY 123 MAIN, GRANT, MI
2150.00
0.05
6
 SMITH, WILLIAM 102 RAYMOND, ADA, MI
4912.50
0.05
12
 BROWN, SAM 419 HARPER, LANSING, MI
2150.00
0.07

Excel Spreadsheet with Table Data

Back to 531 page