YO19_Access_Ch07_PS1 - Advanced Queries 1.0

Step

Instructions

Points Possible

1

Start Access. Open the downloaded Access file named a04ch07_grader_h1.accdb. Grader has automatically added your last name to the beginning of the filename.

0

2

Create a query based on tblTransactions and tblProducts that will calculate the total revenue earned for each ProductType. Add ProductType and a calculated field called Revenue to the Query design grid. The Revenue field should multiply Quantity and UnitPrice.

8

3

Group the results by ProductType, sum the Revenue calculation, format the Revenue field as Currency, and then sort the results in Descending order by Revenue. Name the query qryRevenueByProductType.

10

4

Create a query based on tblCustomers and tblTransactions that will display the FirstName, LastName, StreetAddress, City, State, Zip, and Phone for any resort member who has made a purchase at the Pro Shop during the fourth quarter of 2022. Be sure that each customer is listed only once.

14

5

Sort in Ascending order by LastName. Save the query as qry4thQuarterCustomers. Close the query.

6

6

Create a query based on tblTransactions and tblProducts that calculates total sales volume for each ProductType during the first quarter of 2022. The query should calculate the total quantity of items sold by applying the Sum aggregate function to the Quantity field. Rename this field Q1SalesVolumeByType. Group the results by ProductType.

15

7

Save the query as qryQ1SalesVolumeByType. Close the query.

4

8

Create a query based on tblTransactions that calculates gross sales volume during the first quarter of 2022. The query should calculate the total quantity of items sold by applying the Sum aggregate function to the Quantity field. Rename this field TotalQ1SalesVolume.

14

9

Save the query as qryTotalQ1SalesVolume. Close the query.

4

10

Create a query based on qryQ1SalesVolumeByType and qryTotalQ1SalesVolume that calculates the percentage of quarter 1 sales volume for each ProductType. Your subquery should include the ProductType field and a calculated field named %OfQ1SalesVolumeByType that divides Q1SalesVolumeByType by TotalQ1SalesVolume.

15

11

Format the calculated field as Percent with 2 decimal places. Sort the results in Descending order by %OfQ1SalesVolumeByType.

6

12

Save the query as qryQ1PercentByTypeSalesVolume. Close the query.

4

13

Blank

0

14

Blank

0

15

Exit Access, and then submit your file as directed.

0

Total Points

100