MIS Questions

2019-11-27

[Open the Access file, Lab09 AdventureWorks (Exam) and save it as Lab09 AdventureWorks (your first name), then start working on the questions provided below.]

1. Default Value - Set the default value for ShipMethodID to be 1 in table SalesOrderHeader.

2. Validation Rule – Set a Validation Rule in table SalesOrderHeader that OrderDate should be earlier than or equal to ShipDate. Provide appropriate Validation Text to display an error message if the Validation Rule is broken upon data entry.

3. Query3_Customer Name and Total Due – Create a query that displays CustomerID and AccountNumber of table Customer, customer store name of table Store, and TotalDue and DueDate of table SalesOrderHeader that has DueDate in the fourth quarter in 2001 (that is, from 10/1/2001 till 12/31/2001). Name the query as “Query3_Customer Name and Total Due.”

4.  Query4_Line Total – Create a query, “Query4_Line Total,” that displays SalesOrderID, ProductID, Name, OrderQty, UnitPrice, DiscountedPrice in currency format that is15% discount to UnitPrice, and LineTotal in currency format that is [OrderQty]*[DiscountedPrice].  You need use a few related tables for the query.   

5. Query5_Sum of TotalDue – By using table SalesOrderHeader, create a query, “Query5_Sum of TotalDue” that displays sum of TaxAmt and sum of TotalDue, grouped by the DueDate that has sum of TotalDue more than half million dollars.

6. Query6_Orders by Country and ProductCategory – Using query OrderedItems, create a new query, “Query6_Orders by Country and ProductCategory,” that displays Country, ProductCategory, and Sum of OrderQty, grouped by ProductCategory.

7. Crosstab7_ Orders by Country and ProductCategory – Create a crosstab, “Crosstab7_ Orders by Country and ProductCategory” that rearranges the display from Query6_Orders by Country and ProductCategory, to display countries in the row heading and sum of OrderQty by ProductCategory in columns.

8. Crosstab8_Sales by Country and ProductCategory – Using the query, “Crosstab7_ Orders by Country and ProductCategory,” create a crosstab, “Crosstab8_Sales by Country and ProductCategory” that displays countries in row heading and the sum of sale amounts by ProductCategory in columns. The sale amount is calculated as [OrderQty]*[UnitPrice]. (Hint: First, you need to copy & paste of “Crosstab7_ Orders by Country and ProductCategory,” and rename it to “Crosstab8_Sales by Country and ProductCategory.” Then modify it to calculate the sale amounts.)

9. Set up a table-based lookup table for ShipMethodID of table SalesOrderHeader that replaces its ShipMethodID numbers with the names of shipping method in table ShipMethod. (Hint: You need to delete the relationship between two tables in the Relationship Diagram, then use the Lookup Wizard for ShipMethodID field in table SalesOrderHeader.) Once a Lookup table is set up, change the relationship between two tables into 1-to-many relationship in the Relationship Diagram.

[Save the Access file, Lab09 AdventureWorks (your first name).]