Access Lab Assignment

Work on the provided Access file, BUS506E Lab Exam to answer the questions below.

1. On the table, SalesOrderHeader, provide the grand total of SubTotal at the bottom row of the records. You may need to convert the Short Text into Numeric data attribute for the SubTotal.

2. Set a validation rule on the table SalesOrderHeader such that OrderDate should be earlier than ShipDate and provide an error message when the rule is not kept during the data entry on the OrderDate and ShipDate.

3. Set up a table-based Lookup table for the data field ProductCategoryID of the table ProductSubcategory, that displays the names for the product categories in the table ProductCategory, instead of the numbers.

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 Octerber 1st till December 31st, 2001.

5. We want to know how much were sold by each product in year 2001. Set up a query, SalesByProduct_query, that displays ProductID, Name of the Product, and sum of LineTotal of SalesOrderDetail in the year 2001.

6. Close the SalesByProduct_query from the right side window. In the All Access Objects window, create a new query, SalesByProductWithNoSale_query, by Right-clicking on the SalesByProduct_query, copying, pasting, and renaming. Using Left Join or Right Join, modify the SalesByProductWithNoSale_query, that displays all the products that includes the not-sold products too.

7. Table SalesOrderHeader is set in an inconvenient way because it requires to enter SubTotal, TaxAmt, Freight, and TotalDue individually. These data fields can be readily calculated if we set up a query. Create a query, SalesOrderHeader_extension that displays SalesOrderID, SubTotal (sum of LineTotal of SalesOrderDetail), TaxAmt (8% of LineTotal), Freght (3% of LineTotal), and TotalDue (111% of LineTotal) in Currency format.

8. Using OrderedItems query, set up a crosstab, SaleByCustomerAndProductCategory_crosstab that displays sum of sale amount ([UnitPrice]*[OrderQty]) by each customer and each product category, with the SaleTotal (SaleTotal: [UnitPrice]*[OrderQty]) per customer.

Attachment:- Lab Database.rar