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

Vegas Tours Souvenir Shop Database

Project Description:

The Vegas Tours Souvenir Shop is where Las Vegas bus tours start and end, seven days a week. The shop sells a variety of Vegas souvenirs to customers who take the tour and to those who simply want to purchase souvenirs, either in person or online. Managers has been using an Access database to keep track of customers, products, and transactions. You have been given some sample sales data from February 2015. Managers has asked you to create queries that will help them manage their business more efficiently.

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_h1.accdb. Save the file with the name a03ws07VegasTours_LastFirst replacing LastFirst with your name. Enable the content, if necessary. / 0
2 / Create a query in Design view based on tblPurchase and tblPurchaseLine that displays the PurchaseDate from tblPurchase and ProductID and Quantity from tblPurchaseLine (in that order) and shows the total of each product sold on a particular date. / 7
3 / Group the results by PurchaseDate, and then sort the results in Ascending order by PurchaseDate and ProductID. Rename the Quantity field as TotalSold. Save the query as qrySoldByDate. / 6
4 / Run the query. In Datasheet view, add a total to the bottom of the TotalSold field that displays the total items sold. Save and close the query. / 4
5 / Create a query in Design view based on tblProduct and tblPurchaseLine that displays Category from tblProduct in the first column and a calculated field named GrossSales in the second column that shows the gross sales for each category. GrossSales is calculated by multiplying the cost of the item by a markup of 40% times the quantity of items purchased. / 10
6 / Group the results by Category, sum the GrossSales, and then format GrossSales as Currency. Save the query as qryGrossSales. / 6
7 / Run the query. In Datasheet view, add a total to the bottom of the GrossSales field that displays the total gross sales. Save and close the query. / 4
8 / Create a query based on qryGrossSales that displays GrossSales and shows the total sales. / 7
9 / Format GrossSales as Currency. Save the query as qryTotalGrossSales and then close the query. / 6
10 / Create a query in Design view based on qryGrossSales and qryTotalGrossSales that displays Category and GrossSales from qryGrossSales and a calculated field named PctToGrossSales that shows what percentage of each category’s sales contributed to the gross revenue (in that order). PctToGrossSales is calculated by dividing GrossSales by the SumOfGrossSales. Format PctOfGrossSales as Percent. Save the query as qryPctOfSales. / 12
11 / Run the query. In Datasheet view, add a total to the bottom of the PctOfGrossSales field that displays 100 percent. Save and close the query. / 4
12 / Create a query in Design view based on tblProduct that displays ProductDescription, Category, a calculated field named Volume that shows the physical volume of each item by multiplying Height*Length*Depth. Add the fields in the specified order. / 10
13 / To the right of the Volume field, add a calculated field named RemainingVolume that determines which items can fit into the smallest shipping box, which has a total volume of 142 cubic inches. Save the query as qryPhysicalVolume and then close the query. / 8
14 / Create a query based on qryPhysicalVolume that displays ProductDescription, Category, and a calculated field named PctOfPhysicalVolume (in that order). Calculate the percentage of physical volume for each item if the smallest shipping box has a total volume of 142 cubic inches. / 10
15 / Only display products that are in the Baby category. Format PctOfPhysicalVolume as Percent. Save the query as qryPctOfPhysicalVolume and then close the query. / 6
16 / Exit Access, and then submit your file as directed. / 0
Total Points / 100

Updated: 05/13/2014 1 A_WS07_YOV2_H1_Instructions.docx