Hello, I am having some difficulty with this entire assignment, was hoping someone could help me out if possible! Thanks 
The purpose of this assignment is to examine how information systems can be used to support decision making. In this particular case, a database can be used to develop a Reporting System which can aid the management functions of planning and control. Specifically you will:
a) Use MS Access to create each of the five tables given below. After the tables are created, go into the relationship view and add links between related fields (e.g. custnum in the Customer Table should be linked to custnum in the Orders table and so forth).
b) Create a query which combines the SLSREP and the CUSTOMER tables, and reports CUSTNUM, CUSTNAME, SLSRNAME and SLSRADDR information. In other words, display the name and address for each customer's sales representative with a query.
c) Construct another query which reports the same information as in part b), but now only for sales representative 3.
d) Construct a query which reports the same information as in part c), but now only for those customers who have beginning balance over $500.
e) 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.
f) 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”.
g) 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. You don't have to change the value for TOTCOMM in the table, just report what it will eventually be updated to.
h) 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 quantity, and whether the part should be re-ordered or not.
j) Design a query that you think is interesting and useful.
k) Design reports for items f, g and h.
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 311
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
The purpose of this assignment is to examine how information systems can be used to support decision making. In this particular case, a database can be used to develop a Reporting System which can aid the management functions of planning and control. Specifically you will:
a) Use MS Access to create each of the five tables given below. After the tables are created, go into the relationship view and add links between related fields (e.g. custnum in the Customer Table should be linked to custnum in the Orders table and so forth).
b) Create a query which combines the SLSREP and the CUSTOMER tables, and reports CUSTNUM, CUSTNAME, SLSRNAME and SLSRADDR information. In other words, display the name and address for each customer's sales representative with a query.
c) Construct another query which reports the same information as in part b), but now only for sales representative 3.
d) Construct a query which reports the same information as in part c), but now only for those customers who have beginning balance over $500.
e) 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.
f) 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”.
g) 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. You don't have to change the value for TOTCOMM in the table, just report what it will eventually be updated to.
h) 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 quantity, and whether the part should be re-ordered or not.
j) Design a query that you think is interesting and useful.
k) Design reports for items f, g and h.
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 311
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
