Project Description:

The Painted Treasures Gift Shop sells many products for the resort patrons including jewelry, clothing, and spa products. A database has been started to keep track of the customers, purchases, and products. There are no reports, forms, or queries built yet, so the staff feels like the database is not easy to use. You will create reports, forms and queries to help the staff better manage the data in the database.

 

Steps to Perform:

Step Instructions Points Possible
1 Start Access. Open the downloaded file named a02_grader_h2_Giftshop.accdb. Save the file with the name a02_grader_h2_Giftshop_LastFirst, replacing LastFirst with your name. In the Security Warning bar, click Enable Content. 0
2 Use the Report Wizard to create a report showing customers and their purchases. Include LastName, FirstName, PurchaseDate, Quantity, ProductDescription, Category, and Price. Accept the default view and add no additional grouping. Sort by Category and then by ProductDescription, both in ascending order. Save the report as rptCustomerPurchases_aStudent. 5
3 Change to Layout view, click on the Purchase Date label and textbox and open the Format tab on the Property Sheet. Type 1.1″ in the Width, and 2.5 in the Left. Close the Property Sheet. Change the report title to Customer Purchases. Apply the downloaded theme a02_grader_h2_Giftshop.thmx to all objects in the database. Add Created by a Student to the report footer. Save and close the report. 8
4 Use the Form Wizard to create a form for inputting customers. Select all fields from tblCustomer and accept all defaults. Name the form frmCustomerInput_aStudent. Add Created by a Student to the form footer. Change the form title to Customer Input. 5
5 Change to Layout view. Click the label and textbox for CustomerID and delete them. Adjust the right margin of LastName to be as narrow as FirstName (Width = 1.5″ in the Property Sheet). Adjust the right margin of StreetAddress to be as narrow as City. (Width = 1.8646″ in the Property Sheet.) Change the Shape Fill for the LastName and FirstName textboxes to be Red, Accent 2 Lighter 80%. 8
6 Insert the downloaded a02_grader_h2_Giftshop.jpg into the form. In the Property Sheet, on the Format tab, change the Width to 2.5″ and the Height to 1.1″. Move the image to the right of the customer fields so that it aligns with the top of LastName. Save the form. 5
7 In Form view, change the first record to have a LastName of Student and a FirstName of Access. Close the form. 3
8 Use the appropriate query wizard to create a query to see which customers made multiple purchases using all the fields in tblPurchase (hint: this is a Find Duplicates Query). Show all fields available. Save the query as qryMultiplePurchases_aStudent. In Design view, sort in ascending order on CustomerID and PurchaseID. Save and close the query. 6
9 Use the appropriate query wizard to create a query to see if any customers have made no purchases. Show LastName, FirstName and ResortHotelRoom in that order. Save the query as qryCustomerWithoutPurchase_aStudent. Close the query. 5
10 Use Query Design to create a query to find customers who made purchases but have no ResortHotelRoom. Include LastName, FirstName, PurchaseDate, and ResortHotelRoom in that order. Do not show ResortHotelRoom in the results. Sort in ascending order by LastName and FirstName. In Datasheet view Autofit all column widths. Save the query as qryNonGuestPurchases_aStudent. Close the query. 8
11 Use Query Design to create a query to show customers that have purchased Indigo5 or Spa category products in January 2018. Include LastName, FirstName, PurchaseDate, and Category in that order. In Design view, sort by LastName and FirstName, both in ascending order. Autofit the fields. Save the query as qrySpaIndigoJanuary_aStudent. Close the query. 10
12 Use Query Design to create a query to calculate the extended amount for each product purchased. Include PurchaseID, PurchaseDate, PurchaseLine, Quantity, ProductDescription, and Price in that order. Save the query as qryExtendedAmount_aStudent. Add a new calculated field to the query to calculate each product’s extended amount due based on the quantity and the price. Name the new field ExtendedAmount. Sort the query by PurchaseID in ascending order. In Datasheet view Autofit all column widths. Save the changes and close the query. 12
13 Use the Report Wizard to create a report showing all complete purchases. Include all the fields from qryExtendedAmount_aStudent. Group by PurchaseID. Sort by PurchaseLine in ascending order. Change Orientation to Landscape, accept all other default options, and name the report rptPurchases_aStudent. Add Created by a Student to the report footer. 10
14 Change to Layout view. Change the report title to Purchase Report. Change the width of the PurchaseID to 1″. Change the width of the Purchase Line column header and textbox in the Property Sheet to 1″ and the left to 2.0″. Change the width of the PurchaseDate column header and textbox to 1″. 5
15 Click ExtendedAmount and add a sum subtotal. Open the Property Sheet and change the Format of the new control to Currency. Add a caption and change it to Total Purchase. Repeat these revisions for the grand total control and change the caption to Grand Total Purchases. 5
16 Change the subtotal Total Purchase caption and text box to bold with a Shape Fill of Red, Accent 2 Lighter 80%. Change the grand total caption and text box to bold with a Shape Fill of Red, Accent 2, Lighter 60%. Save and close the report. 5
17 Close all database objects. Close the database and then exit Access. Submit the database as directed. 0
Total Points 100