Management Information System
Access Lab Examination
2022-3-15
[General Guidance]
Work on the provided Access file, “MIS Lab Exam 2022-3-15.accdb” to solve the problems below. When you have finished the lab works, please post the result on the Google Classroom by 9 pm, 3-15-2022. Later submission will have some points subtracted.
1. On the table SalesOrderHeader, set a validation rule such that OrderDate should be earlier than DueDate and provide an error message when the rule is not kept during the data entry on the OrderDate and DueDate.
2. On the table StateProvince, set up a table-based Lookup table for the data field CountryRegionCode that displays the names of the table CountryRegion, instead of the abbreviated CountryRegionCode.
3. Using the table SalesOrderHeader, provide grand total of the total due (TotalDue) at the bottom of the worksheet display of SalesOrderHeader.
4. Using the table SalesOrderHeader, set up a query, SalesQ4_query, that displays SalesOrderID, OrderDate, TotalDue, for the orders made in the fourth quarter of year 2001, that is, from 10/1/2001 to 12/31/2001 inclusive. “Inclusive” means dates 10/1/2001 and 12/31/2001 are included in the Q4 range.
5. Using the table SalesOrderHeader, we want to know how much each SalesPerson has sold in year 2001. Set up a query, SalesPerformance_query, that displays SalesPersonID and TotalSale that is sum of SubTotal in the year 2001 in the Currency format and in descending order.
6. Using table SalesOrderDetail and table Product, create a query ProductSales_query that displays ProductID (from table Product), Name (from table Product), LineTotal (from table SalesOrderDetail) in descending order, including the products that didn’t make any sale even. You will need Left Join or Right Join to display all the products in the table Product.
7. Create a query, SalesByStateProvince_query that displays StateProvinceCode of customers (from table StateProvince) and “SumOfTotalDue” that is the sum of TotalDue (from table SalesOrderHeader) in descending order and in currency format for each StateProvinceCode. Use Relationship Diagram to find related tables for the query.
8. Using OrderedItems query, set up a crosstab, SaleByCustomerAndProductCategory_Crosstab that displays sum of sale amount ([UnitPrice]*[OrderQty]) by each Customer and each ProductCategory, and SaleTotal that is sum of [UnitPrice]*[OrderQty] per each customer.
AddressID AddressLine1 AddressLine2 City StateProvinceID PostalCode
1 1970 Napa Ct. Bothell 79 98011
2 9833 Mt. Dias Blv. Bothell 79 98011
3 7484 Roundtree Drive Bothell 79 98011
4 9539 Glenside Dr Bothell 79 98011
5 1226 Shoe St. Bothell 79 98011
6 1399 Firestone Drive Bothell 79 98011
7 5672 Hale Dr. Bothell 79 98011
8 6387 Scenic Avenue Bothell 79 98011
9 8713 Yosemite Ct. Bothell 79 98011
10 250 Race Court Bothell 79 98011
11 1318 Lasalle Street Bothell 79 98011
12 5415 San Gabriel Dr. Bothell 79 98011
13 9265 La Paz Bothell 79 98011
14 8157 W. Book Bothell 79 98011
15 4912 La Vuelta Bothell 79 98011
16 40 Ellis St. Bothell 79 98011
17 6696 Anchor Drive Bothell 79 98011
18 1873 Lion Circle Bothell 79 98011
19 3148 Rose Street Bothell 79 98011
20 6872 Thornwood Dr. Bothell 79 98011
21 5747 Shirley Drive Bothell 79 98011
22 636 Vine Hill Way Portland 58 97205
23 6657 Sand Pointe Lane Seattle 79 98104
24 80 Sunview Terrace Duluth 36 55802
25 9178 Jumping St. Dallas 73 75201
26 5725 Glaze Drive San Francisco 9 94109
27 2487 Riverside Drive Nevada 74 84407
28 9228 Via Del Sol Phoenix 6 85004
29 8291 Crossbow Way Memphis 72 38103
30 9707 Coldwater Drive Orlando 15 32804
31 9100 Sheppard Avenue North Ottawa 57 K4B 1T7
32 26910 Indela Road Montreal 63 H1Y 2H5
33 10203 Acorn Avenue Calgary 1 T2P 2G8
34 94, rue Descartes Bordeaux 119 33000
35 Pascalstr 951 Berlin 20 14111
36 34 Waterloo Road Melbourne 77 3000
37 Downshire Way Cambridge 14 BA5 3HX
38 8154 Via Mexico Detroit 35 48226
39 3997 Via De Luna Cambridge 30 02139
40 1902 Santa Cruz Bothell 79 98011
41 793 Crawford Street Kenmore 79 98028
42 463 H Stagecoach Rd. Kenmore 79 98028
43 5203 Virginia Lane Kenmore 79 98028
44 4095 Cooper Dr. Kenmore 79 98028
45 6697 Ridge Park Drive Kenmore 79 98028
46 5669 Ironwood Way Kenmore 79 98028
47 8192 Seagull Court Kenmore 79 98028
48 5553 Cash Avenue Kenmore 79 98028
49 7048 Laurel Kenmore 79 98028
50 25 95th Ave NE Kenmore 79 98028
51 3280 Pheasant Circle Snohomish 79 98296
52 4231 Spar Court Snohomish 79 98296
53 1285 Greenbrier Street Snohomish 79 98296
54 5724 Victory Lane Snohomish 79 98296
55 591 Merriewood Drive Snohomish 79 98296
56 3114 Notre Dame Ave. Snohomish 79 98296
57 7230 Vine Maple Street Snohomish 79 98296
58 2601 Cambridge Drive Snohomish 79 98296
59 2115 Passing Snohomish 79 98296
60 4852 Chaparral Court Snohomish 79 98296
61 7726 Driftwood Drive Monroe 79 98272
62 3841 Silver Oaks Place Monroe 79 98272
63 9652 Los Angeles Monroe 79 98272
64 4566 La Jolla Monroe 79 98272
65 1356 Grove Way Monroe 79 98272
66 4775 Kentucky Dr. Unit E Monroe 79 98272
67 4734 Sycamore Court Monroe 79 98272
68 896 Southdale Monroe 79 98272
69 2275 Valley Blvd. Monroe 79 98272
70 1792 Belmont Rd. Monroe 79 98272
71 5734 Ashford Court Monroe 79 982
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.