Vegas Tours Souvenir Shop Database
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.
For the purpose of grading the project you are required to perform the following tasks:
Start Access. Open the downloaded Access file nameda03ws06_grader_a1.accdb. Save the file with the namea03ws06VegasTours_LastFirst, replacing LastFirst with your name. Enable the content, if necessary.
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.
Sort the results in Descending order by Cost. Only display the products with the top 25% of cost. Save the query asqryShirtInventory and then close the query.
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.
Sort the results in Ascending order by LastName. Save the query as qryCouponList and then close the query.
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.
Format the retail price field as Currency. Save the query asqryRetailPrice and then close the query.
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.