Office 2013 – myitlab:grader – Instructions Your Office Series Vol 2

Advanced Queries with the Golf Pro Shop Database

Project Description:

Management at the Golf Pro Shop has been collecting and storing their sales data in an Access database and need your help to create some queries to help them to begin to make sound business decisions based on data.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step / Instructions / Points Possible /
1 / Start Access. Open the downloaded Access file named a03ws07_grader_a1.accdb. Save the file with the name a03ws07ProShop_LastFirst replacing LastFirst with your name. Enable the content if necessary. / 0
2 / Create a query in Design view based on tblTransactions and tblProducts that will calculate the total revenue earned for each ProductType. Add ProductType and a calculated field named Revenue to the Query design grid (in that order). The Revenue field should multiply Quantity and UnitPrice. / 10
3 / Group the results by ProductType, format the Revenue field as Currency, and then sort the results in Descending order by Revenue. Save the query as qryRevenueByProductType. Close the query. / 10
4 / Create a query in Design view based on tblCustomers and tblTransactions that will display the FirstName, LastName, StreetAddress, City, State, Zip, and Phone (in that order) for any resort member who has made a purchase at the Pro Shop during the first quarter of 2015 (between 1/1/2015 and 3/31/2015). Be sure that each customer is only listed once. / 14
5 / Sort in Ascending order by LastName. Save the query as qry1stQuarterCustomers. Close the query. / 6
6 / Create a query in Design view based on tblTransactions and tblProducts that calculates total sales volume for each ProductType during the first quarter of 2015. The query should include a calculated field named Q1SalesVolumeByType that totals the Quantity field. Do not display the TransactionDate field in the results. Add the fields in the following order: ProductType, Q1SalesVolumeByType, TransactionDate. / 12
7 / Save the query as qryQ1SalesVolumeByType. Close the query. / 6
8 / Create a query in Design view based on tblTransactions and tblProducts that calculates gross sales volume during the first quarter of 2015. The query should display a calculated field in the first column named TotalQ1SalesVolume that totals the Quantity field. Do not display the TransactionDate field in the results. / 12
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. / 12
11 / Format the calculated field as Percent with 2 decimal places. Sort the results in Descending order by %OfQ1SalesVolumeByType. / 10
12 / Save the query as qryQ1PercentByTypeSalesVolume. Close the query. / 4
13 / Exit Access, and then submit your file as directed. / 0
Total Points / 100

Updated: 05/17/2014 1 A_WS07_YOV2_A1_Instructions.docx