Assignment

Using the file InvoiceData.accdb, complete the reports & queries below. Before submitting your work, make sure your file has two reports and seven queries.

1) Reports:

For each OrderPriority, show the average of Sales, grouped by Province. You can include the averages using the "Summary Options" button in the third Report Wizard window, where you select ascending or descending order choose the summary (average) only, not summary and details. You should use the Layout View to adjust the size of any text boxes too small to display the Sales values.

Show the sum of Profit for each {Region, Province} pair. Group the records by Region, then Province, and only show the sum of Profit, not other details.

2) Queries:

Show the ID, OrderDate, and ShipDate fields (only) of any records where the OrderDate is between 1/1/2020 and 6/1/2020 and the ProductContainer is Wrap Bag.

For each Region, show the sum of Sales for each ShipMode. You should have one sum for each {Region, ShipMode} pair.

Show the Sales and Profit fields (only) of any records with Sales at least $500 AND the ShipMode is Express Air.

For each Province, show the sum of Profit from the months of July through September (use OrderDate). The sum should be for the entire three-month range, with separate totals for each year.

Show all fields of any records with Sales above $1200 AND the ProductCategory is Technology.

Show average Profit for each OrderPriority, for all records with a 2019 ShipDate. You should only have one average value per OrderPriority level.

Show all fields of any records where the ShipMode is Express Air and the ProductContainer is not Small Box or Small Pack (in the query criteria, you can use the <> operator for not equal to).