Assignment: Management Information System
Access Lab Examination
Work on the provided Access file, "MIS Lab Exam 2018-11-13" to solve the problems below. When you have finished the lab works, send the result Access file to the instructor.
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 sum of the total due (TotalDue) at the bottom of the query display. To get the sum of the TotalDues, the data type for the TotalDue needs to be changed to some other type.
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 sum of SubTotal in the year 2001.
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 (from table StateProvince) and "SumOfTotalDue" as 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, with the SaleTotal (SaleTotal: [UnitPrice]*[OrderQty]) per each customer.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also include a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also Include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- MIS-Lab-Exam.rar