Set the data field ShipMethodID of the table, SalesOrderHeader

Work on the provided Access file .accdb for the questions below.  .

1. Set the data field ShipMethodID of the table, SalesOrderHeader, to a default value of 1 and make it not to leave it blank (that is, the field data is required always).

2. Set a validation rule on the table Product such that StandardCost should be less than ListPrice with an appropriate remark when the rule is not kept.

3. Set up a table-based Lookup table for the data field CountryRegionCode of the table StateProvince, that displays the name for the CountryRegionCode in the table CountryRegion, instead of the country region codes.

4. Using the table SalesOrderHeader, set up a query, SalesQ3_query, that displays SalesOrderID, OrderDate, TotalDue, for the order in the third quarter of year 2001, that is from June 1st till September 30th, 2001.

5. We want to know how much were sold by each subcategory in year 2001. Set up a query, SalesBySubcategory_query, that displays ProductSubcategoryID, Name of the ProductSubcategory, and sum of LineTotal of SalesOrderDetail in the year 2001.

6. Close the SalesBySubcategory_query from the right side window. In the All Access Objects window, create a new query, SalesBySubcategoryWithNoSale_query, by Right-clicking on the SalesBySubcategory_query, copying, pasting, and renaming.  Using Left Join or Right Join, modify the previous query, SalesBySubcategoryWithNoSale_query and set up a new query, that displays all the Subcategoris that include the not-sold Subcategories 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.