+1443 776-2705 panelessays@gmail.com
  

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 98272
72 5030 Blue Ridge Dr. Monroe 79 98272
73 158 Walnut Ave Monroe 79 98272
74 8310 Ridge Circle Monroe 79 98272
75 3747 W. Landing Avenue Monroe 79 98272
76 2598 La Vista Circle Duvall 79 98019
77 9693 Mellowood Street Duvall 79 98019
78 1825 Corte Del Prado Duvall 79 98019
79 5086 Nottingham Place Duvall 79 98019
80 3977 Central Avenue Duvall 79 98019
81 8209 Green View Court Duvall 79 98019
82 8463 Vista Avenue Duvall 79 98019
83 5379 Treasure Island Way # 14 Duvall 79 98019
84 3421 Bouncing Road Duvall 79 98019
85 991 Vista Verde Duvall 79 98019
86 390 Ridgewood Ct. Carnation 79 98014
87 1411 Ranch Drive Carnation 79 98014
88 9666 Northridge Ct. Carnation 79 98014
89 3074 Arbor Drive Carnation 79 98014
90 9752 Jeanne Circle Carnation 79 98014
91 7166 Brock Lane Seattle 79 98104
92 7126 Ending Ct. Seattle 79 98104
93 4598 Manila Avenue Seattle 79 98104
94 5666 Hazelnut Lane Seattle 79 98104
95 1220 Bradford Way Seattle 79 98104
96 5375 Clearland Circle Seattle 79 98104
97 2639 Anchor Court Seattle 79 98104
98 502 Alexander Pl. Seattle 79 98104
99 5802 Ampersand Drive Seattle 79 98104
100 5125 Cotton Ct. Seattle 79 98104
101 3243 Buckingham Dr. # 207 Seattle 79 98104
102 3029 Pastime Dr # 2 Seattle 79 98104
103 9537 Ridgewood Drive Seattle 79 98104
104 9964 North Ridge Drive Seattle 79 98104
105 1619 Stillman Court Seattle 79 98104
106 2144 San Rafael Seattle 79 98104
107 7403 N. Broadway Seattle 79 98104
108 7842 Ygnacio Valley Road Seattle 79 98104
109 874 Olivera Road Seattle 79 98104
110 1064 Slow Creek Road Seattle 79 98104
111 77 Birchwood Seattle 79 98104
112 7765 Sunsine Drive Seattle 79 98104
113 1102 Ravenwood Seattle 79 98104
114 1398 Yorba Linda Seattle 79 98104
115 4948 West 4th St Seattle 79 98104
116 8290 Margaret Ct. Seattle 79 98104
117 426 San Rafael Seattle 79 98104
118 136 Balboa Court Seattle 79 98104
119 7439 Laguna Niguel Seattle 79 98104
120 6498 Mining Rd. Seattle 79 98104
121 6578 Woodhaven Ln. Seattle 79 98104
122 2354 Frame Ln. Seattle 79 98104
123 6843 San Simeon Dr. Seattle 79 98104
124 7616 Honey Court Seattle 79 98104
125 931 Corte De Luna Seattle 79 98104
126 7594 Alexander Pl. Seattle 79 98104
127 7127 Los Gatos Court Seattle 79 98104
128 8656 Lakespring Place Seattle 79 98104
129 5025 Holiday Hills Seattle 79 98104
130 5376 Catanzaro Way Seattle 79 98104
131 504 O St. Edmonds 79 98020
132 6870 D Bel Air Drive Edmonds 79 98020
133 8152 Claudia Dr. Edmonds 79 98020
134 6057 Hill Street Edmonds 79 98020
135 2812 Mazatlan Edmonds 79 98020
136 172 Turning Dr. Edmonds 79 98020
137 9784 Mt Etna Drive Edmonds 79 98020
138 2059 Clay Rd Edmonds 79 98020
139 2687 Ridge Road Edmonds 79 98020
140 371 Apple Dr. Edmonds 79 98020
141 3281 Hillview Dr. Edmonds 79 98020
142 2080 Sycamore Drive Edmonds 79 98020
143 7511 Cooper Dr. Edmonds 79 98020
144 9825 Coralie Drive Edmonds 79 98020
145 8411 Mt. Orange Place Edmonds 79 98020
146 207 Berry Court Edmonds 79 98020
147 1061 Buskrik Avenue Edmonds 79 98020
148 3632 Bank Way Edmonds 79 98020
149 3026 Anchor Drive Edmonds 79 98020
150 7338 Green St. Edmonds 79 98020
151 2038 Encino Drive Edmonds 79 98020
152 2466 Clearland Circle Edmonds 79 98020
153 9277 Country View Lane Edmonds 79 98020
154 5423 Champion Rd. Edmonds 79 98020
155 2046 Las Palmas Edmonds 79 98020
156 8684 Military East Bellevue 79 98004
157 7270 Pepper Way Bellevue 79 98004
158 6058 Hill Street # 4 Bellevue 79 98004
159 1648 Eastgate Lane Bellevue 79 98004
160 3454 Bel Air Drive Bellevue 79 98004
161 3067 Maya Bellevue 79 98004
162 3197 Thornhill Place Bellevue 79 98004
163 3919 Pinto Road Bellevue 79 98004
164 7396 Stratton Circle Bellevue 79 98004
165 9745 Bonita Ct. Bellevue 79 98004
166 3670 All Ways Drive Bellevue 79 98004
167 7221 Peachwillow Street Bellevue 79 98004
168 1343 Prospect St Bellevue 79 98004
169 6448 Castle Court Bellevue 79 98004
170 7902 Grammercy Lane Unit A Bellevue 79 98004
171 8668 Via Neruda Bellevue 79 98004
172 4777 Rockne Drive Bellevue 79 98004
173 620 Woodside Ct. Bellevue 79 98004
174 6307 Greenbelt Way Bellevue 79 98004
175 3284 S. Blank Avenue Bellevue 79 98004
176 8751 Norse Drive Bellevue 79 98004
177 6968 Wren Ave. Bellevue 79 98004
178 4311 Clay Rd Bellevue 79 98004
179 771 Northridge Drive # 495 Bellevue 79 98004
180 2947 Vine Lane Bellevue 79 98004
181 9320 Teakwood Dr. Bellevue 79 98004
182 3711 Rollingwood Dr Bellevue 79 98004
183 6118 Grasswood Circle Bellevue 79 98004
184 5678 Clear Court Bellevue 79 98004
185 2681 Eagle Peak Bellevue 79 98004
186 332 Laguna Niguel Bellevue 79 98004
187 7808 Brown St. Bellevue 79 98004
188 6774 Bonanza Bellevue 79 98004
189 3708 Montana Bellevue 79 98004
190 9435 Breck Court Bellevue 79 98004
191 6951 Harmony Way Sammamish 79 98074
192 6871 Thornwood Dr. Sammamish 79 98074
193 9104 Mt. Sequoia Ct. Sammamish 79 98074
194 2095 Sierra Drive Sammamish 79 98074
195 4444 Pepper Way Sammamish 79 98074
196 4525 Benedict Ct. Sammamish 79 98074
197 7820 Bird Drive Sammamish 79 98074
198 1962 Ferndale Lane Sammamish 79 98074
199 5263 Etcheverry Dr Sammamish 79 98074
200 2473 Orchard Way Sammamish 79 98074
201 6202 Seeno St. Sammamish 79 98074
202 4096 San Remo Sammamish 79 98074
203 7086 O St. Sammamish 79 98074
204 4310 Kenston Dr. Sammamish 79 98074
205 7939 Bayview Court Sammamish 79 98074
206 6097 Mt. McKinley Ct. Redmond 79 98052
207 3066 Wallace Dr. Redmond 79 98052
208 9006 Woodside Way Redmond 79 98052
209 9906 Oak Grove Road Redmond 79 98052
210 2383 Pepper Drive Redmond 79 98052
211 6369 Ellis Street Redmond 79 98052
212 8000 Crane Court Redmond 79 98052
213 9322 Driving Drive Redmond 79 98052
214 3768 Door Way Redmond 79 98052
215 101 Candy Rd. Redmond 79 98052
216 2427 Notre Dame Ave. Redmond 79 98052
217 1960 Via Catanzaro Redmond 79 98052
218 9863 Ridge Place Redmond 79 98052
219 3397 Rancho View Drive Redmond 79 98052
220 4909 Poco Lane Redmond 79 98052
221 7297 RisingView Redmond 79 98052
222 7723 Firestone Drive Redmond 79 98052
223 1275 West Street Redmond 79 98052
224 2137 Birchwood Dr Redmond 79 98052
225 249 Alexander Pl. Redmond 79 98052
226 2482 Buckingham Dr. Redmond 79 98052
227 8467 Clifford Court Redmond 79 98052
228 9241 St George Dr. Everett 79 98201
229 3928 San Francisco Everett 79 98201
230 475 Santa Maria Everett 79 98201
231 181 Gaining Drive Everett 79 98201
232 3385 Crestview Drive Everett 79 98201
233 1185 Dallas Drive Everett 79 98201
234 7883 Missing Canyon Court Everett 79 98201
235 5452 Corte Gilberto Everett 79 98201
236 9008 Creekside Drive Everett 79 98201
237 5242 Marvelle Ln. Everett 79 98201
238 4098 Woodcrest Dr. Everett 79 98201
239 8624 Pepper Way Everett 79 98201
240 2176 Apollo Way Everett 79 98201
241 1962 Cotton Ct. Everett 79 98201
242 1362 Somerset Place Everett 79 98201
243 3238 Laguna Circle Everett 79 98201
244 3665 Oak Creek Ct. Everett 79 98201
245 7640 First Ave. Everett 79 98201
246 6629 Polson Circle Everett 79 98201
247 2294 West 39th St. Everett 79 98201
248 1400 Gate Drive Newport Hills 79 98006
249 4350 Minute Dr. Newport Hills 79 98006
250 9297 Kenston Dr. Newport Hills 79 98006
251 8967 Hamilton Ave. Newport Hills 79 98006
252 9687 Shakespeare Drive Newport Hills 79 98006
253 1397 Paradise Ct. Newport Hills 79 98006
254 3030 Blackburn Ct. Newport Hills 79 98006
255 7469 Paradise Ct. Newport Hills 79 98006
256 9605 Pheasant Circle Gold Bar 79 98251
257 2425 Notre Dame Ave Gold Bar 79 98251
258 8036 Summit View Dr. Gold Bar 79 98251
259 213 Stonewood Drive Gold Bar 79 98251
260 3884 Beauty Street # 14 Gold Bar 79 98251
261 1748 Bird Drive Index 79 98256
262 310 Winter Lane Index 79 98256
263 3514 Sunshine Index 79 98256
264 1245 Clay Road Index 79 98256
265 3127 El Camino Drive Index 79 98256
266 7691 Benedict Ct. # 141 Issaquah 79 98027
267 1144 Paradise Ct. Issaquah 79 98027
268 7435 Ricardo Issaquah 79 98027
269 9530 Vine Lane Issaquah 79 98027
270 6580 Poor Ridge Court Issaquah 79 98027
271 5979 El Pueblo Issaquah 79 98027
272 1921 Ranch Road Issaquah 79 98027
273 3848 East 39th Street Issaquah 79 98027
274 5256 Chickpea Ct. Issaquah 79 98027
275 989 Crown Ct Issaquah 79 98027
276 3333 Madhatter Circle Issaquah 79 98027
277 342 San Simeon Issaquah 79 98027
278 9314 Icicle Way Issaquah 79 98027
279 7772 Golden Meadow Issaquah 79 98027
280 8585 Los Gatos Ct. Issaquah 79 98027
281 7985 Center Street Renton 79 98055
282 5980 Icicle Circle Unit H Renton 79 98055
283 1378 String Dr Renton 79 98055
284 9495