Vegas Tours Souvenir Shop Database

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. Management 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. Management has asked you to create some queries to begin the process of analyzing the data that has been collected.

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 a03ws06_grader_a1.accdb. Save the file with the name a03ws06VegasTours_LastFirst, replacing LastFirst with your name. Enable the content, if necessary.    0
2    Create a query based on the tblProduct table that will display the ProductID, ProductDescription, QuantityInStock, Cost, Size, and Color, in that order, for any product with shirt in the ProductDescription field.    10
3    Sort the results in Descending order by Cost. Only display the products with the top 25% of cost. Save the query as qryShirtInventory and then close the query.    7
4    Create a query using the tblCustomer table. Display CustomerID, FirstName, LastName, and a new calculated field named Coupon?, in that order. In the Coupon? field, use the IIf and Like functions to display No Coupon if the customer did not take the tour and Send 10% off next visit if the customer did take the tour.    12
5    Sort the results in Ascending order by LastName. Save the query as qryCouponList and then close the query.    5
6    Create a query based on the tblProduct table that will display the ProductID, ProductDescription, Category, QuantityInStock, Cost, Size, Color, and a new calculated field named Retail Price, in that order. In the Retail Price field, use the Round function to display the price that the store needs to sell its products. The retail price is two times the cost.    12
7    Format the retail price field as Currency. Save the query as qryRetailPrice and then close the query.    5
8    Create a query based on the tblCustomer table that will display the FirstName, LastName, Address, City, State, and ZipCode, in that order. In the LastName field, use a wildcard and the Like function to include customers whose last names begin with letters M through Z.    12
9    Sort the results in Ascending order by LastName. Save your query as qryMZCustomers and then close the query.    5
10    Create a query based on the tblCustomer and tblPurchase tables that will concatenate the member’s first and last names (separated by a space) followed by the date they made a purchase. Name the concatenated field Purchase Details. Use a comma to separate the full name and date, in that order. Save your query as qryPurchaseDetails and then close the query.    12
11    Create a query based on the tblPurchase and tblPurchaseLine tables that will display the PurchaseID, PurchaseDate, PurchaseLine, ProductID, and Quantity in that order to view all transactions that fall within a specific date range.    12
12    Add a parameter that allows you to enter a start and end date, using Enter start date and Enter end date as the prompts. Use 2/5/2015 as the start date and 2/8/2015 as the end date to test your query. Save your query as qryDateRange and then close the query.    8
13    Close all database objects. Close the database and then exit Access. Submit the database as directed.    0
    Total Points    100