1. Open the start fileAC2019-Capstone-Level3.
2. NOTE:If necessary, enable active content by clicking theEnable Contentbutton in the Message Bar.
3. The file will be renamed automatically to include your name. Change theproject filename if directed to do so by your instructor, andsaveit.
4. Create a new table from scratch to track sales.
5. The first field should be anAutoNumberfield named:SaleID
6. The second field should be aDate & Timefield named:SaleDate
7. The third field should be a lookup field. (Hint: Use the Lookup Wizard to create the new field.) It should display theLocationDescriptionfield from theLocationstable. Values in the lookup should be sorted by values in theLocationDescriptionfield. Include theLocationIDfield in the lookup, but do not display it. (Hint: Hide the key column.) Enable data integrity by restricting deletions. Name this field:SaleLocation
8. Save the table with the name:Sales
9. Switch to Design view and modify field properties.
10. Add an input mask to theSaleDatefield. Use theShort Dateinput mask. Do not change any other input mask options.
11. Apply theLong Dateformat to theSaleDatefield.
12. Add a new lookup field as the last field in theSalestable to track payment type. (Hint: Use the Lookup Wizard to create the new field.)
13. Name the field:PaymentType
14. The lookup field should display these values in this order:
15. Cash
16. Credit Card
17. Gift Card
18. Store Credit
19. Limit data entry to values in the list. Do not allow multiple values.
20. Save the table.
21. Switch back to Datasheet view to add sample records to theSalestable.
22. Add three records to the table with the following data. (Hint: Remember, the first field in the table,SaleID, is an AutoNumber field, so there is nothing to enter for each record.)
23. SaleDate
24. SaleLocation
25. PaymentType
26. 10/01/2019
27. Georgetown
28. Credit Card
29. 10/01/2019
30. George Washington University
31. Cash
32. 10/01/2019
33. George Washington University
34. Cash
35. Adjust the width of theSaleDatefield so the entire long date is visible.
36. Save and close the table.
37. Create a new table to capture the details for each sale.
38. The first field should be an AutoNumber field named:SaleDetailID
39. The second field should be a lookup field named:SaleIDThe lookup field should be limited to values in theSaleIDfield of theSalestable. Include only theSaleIDin the lookup field. Enable data integrity by restricting deletions.
40. Save the table as:SaleDetails
41. Add a third field to the far right of the table. Name this field:ItemThis is another lookup field. Include all the fields from theItemstable. Sort the lookup items by values in theItemNamefield. Hide the primary key field. Enable data integrity by restricting deletions.
42. Add aNumberfield to the right of theItemfield. Name the field:Quantity
43. Set the default value for theQuantityfield to:1
44. Add three records to the table with the following data. (Hint: Remember, the first field in the table,SaleDetailID, is an AutoNumber field, so there is nothing to enter for each record.)
45. SaleID
46. Item
47. Quantity
48. 1
49. Chocolate
50. 6
51. 2
52. Sea Salt and Caramel
53. 4
54. 3
55. Sea Salt and Caramel
56. 5
57. Close the table.
58. Use the Form Wizard to create a new form for inputting sales data.
59. Include all the fields from theSalestable.
60. Include theItemandQuantityfields from theSaleDetailstable.
61. View the form data by records in theSalestable with related records in theSaleDetailstable displayed in a subform.
62. The subform should be displayed as a Datasheet.
63. Name the main form:SalesFormand name the subform:SaleDetailsSubform(Hint: Be sure to remove the space between SaleDetails and Subform in the subform name suggested by Access.)
64. Open the form in Form view to review your work.
65. Navigate to the record in the main form for SaleID3and enter sale details in the subform as follows:
66. Item:Original Blend, Quantity:4
67. Item:Old Bay, Quantity:6
68. Close the form.
69. Open theItemstable and modify the table fields as follows:
70. Set theDefault Valueproperty for thePricefield to:7
71. Change the data type for thePricefield to:Currency
72. Autofit the width of theItemNamefield.
73. Save the changes and close theItemstable.
74. Create a Single Record form using theItemstable as the record source. Save the form with the name:SingleRecordForm
75. Create a form to display records from theItemstable in two formats with the Single Record form at the top and a Datasheet form at the bottom.
76. Create aSplit Formbased on theItemstable.
77. Name the form:SplitForm
78. Begin a new blank form in Layout view.
79. From theLocationstable, add theLocationID, LocationDescription,andCommentsfields in that order, at the left side of the form.
80. Widen the labels so that they are just wide enough forLocationDescriptionto be completely visible.
81. From theLocationstable, add theOpenTimefield to the right of theLocationIDcontrols.
82. Reduce the width of theOpenTimebound control so that the control is just wide enough to display the time data.
83. From theLocationstable, add theCloseTimefield to the right of theOpenTimecontrols.
84. Reduce the width of theCloseTimebound control so the control is just wide enough to display the time data.
85. Move theOpenTimeandCloseTimecontrols so they are next to theLocationDescriptioncontrols instead.
86. From theLocationstable, add theDaysfield to the form layout in the empty space to the right of theLocationIDcontrols, above theOpenTimecontrols.
87. Add a logo to the form header. Use this file, located with the resources for this project:toptCornLogo-small.png
88. Save the form with the name:LocationsForm
89. Close the forms.
90. Import theStoreSalestable from theSalesArchiveAccess database (downloaded from the Resources link). Do not open theSalesArchivedatabase.
91. Rename and open theStoreSalestable. Verify that you are working in the database you downloaded from Access, not theSalesArchivedatabase.
92. Rename the table:SalesArchive
93. Delete theTotalfield.
94. Find the record with the ID500and delete it. (Hint: It is the last record in the table.)
95. Find and replace eachItemIDvalueOLDBwithOLDB005.
96. Rename theTotalSalfield to:TotalSale
97. Save and close the table.
98. Import records from the Excel fileNewSalesData(downloaded from the Resources link) and append a copy of the records to theSalesArchivetable.
99. Use the Relationships window to create a relationship between theItemsandSalesArchivetables.
100. Show theSalesArchivetable in the Relationships window.
101. Create a one-to-many relationship between theItemIDfield in theItemstable and theItemIDfield in theSalesArchivetable. You may rearrange the tables in the Relationships window if you want.
102. Enforce referential integrity so a record cannot be deleted or altered in theItemstable if it would cause a conflict with the data in theSalesArchivetable.
103. Close the Relationships window and save the changes.
104. Create a query to display sales of Truffle flavored popcorn from theSalesArchivetable.
105. Include the following fields in this order: theSaleDate,Quantity, andTotalSalefields from theSalesArchivetable and theItemNamefield from theItemstable.
106. Add the criteriaTruffleto theItemNamefield.
107. Hide theItemNamefield in the query results. Run the query to check your work. (Hint: There should be 49 records in the query results.)
108. Save the query asTruffleQryand then close the query.
109. Create a query to display sales of Old BayorTruffle flavored popcorn from theSalesArchivetable
110. Include the following fields in this order: theSaleDate,Quantity, andTotalSalefields from theSalesArchivetable and theItemNamefield from theItemstable.
111. Add the criteriaOld BayorTruffleto theItemNamefield. Run the query to check your work. (Hint: There should be 114 records in the query results.)
112. Save the query asNewFlavorsQryand then close the query.
113. Create a query to display sales for more than $100.00 from theSalesArchivetable.
114. Include the following fields in this order: theSaleDatefrom theSalesArchivetable,ItemNamefield from theItemstable, andTotalSalefrom theSalesArchivetable.
115. Add criteria to theTotalSalefield to return only salesgreater than 100.
116. Specify the sort order in the query, so the results always display the records with the highest values in theTotalSalefield first. Run the query to check your work. (Hint: There should be 15 records in the query results.)
117. Save the query asHighDollarSalesQryand close it.
118. Create a query to display sales for more than $100 of Old Bay flavored popcorn from theSalesArchivetable
119. Include the following fields in this order: theItemNamefield from theItemstable and theSaleDate,Quantity, andTotalSalefields from theSalesArchivetable.
120. Add the criteria to the query to return only records where theItemNameisOld Bayand theTotalSaleisgreater than 100. Run the query to check your work. (Hint: There should be 3 records in the query results.)
121. Save the query asHighDollarOldBayQryand then close the query.
122. Create a query to display sales of Original Blend popcorn on July 4, 2019 from theSalesArchivetable.
123. Include the following fields in this order: theItemNamefield from theItemstable and theSaleDateandQuantityfields from theSalesArchivetable.
124. Add the criteria to the query to return only records where theItemNameisOriginal Blendand the Date isJuly 4, 2019. Run the query to check your work. (Hint: There should be 3 records in the query results.)
125. Save the query asJuly4OriginalBlendQryand then close the query.
126. Create a query to calculate the per unit price of the archived sales.
127. Include the following fields in this order: theItemNamefield from theItemstable and theQuantityandTotalSalefields from theSalesArchivetable.
128. Add a calculated field to the far right of the query to calculate the value of theTotalSale divided by Quantity. Name the field:CostPerUnitRun the query to check your work. (Hint: There should be 240 records in the query results.)
129. Save the query asCostPerUnitQryand then close the query.
130. Create a parameter query to display sales from a specific date.
131. Include these fields in this order: theSaleDatefield from theSalesArchivetable, theItemNamefield from theItemstable, theQuantityandTotalSalefields from theSalesArchivetable.
132. Use the prompt:Enter the sale date:
133. Run the query to check your work. Enter the date7/4/2019when prompted. (Hint: There should be 9 records in the query results.)
134. Save the query asByDateParameterQryand close it.
135. Use the Report Wizard to create a report based on theNewFlavorsQryquery.
136. Include the fields from theNewFlavorsQryquery in this order:ItemName,SaleDate,Quantity, andTotalSale.
137. View the data by theItemstable.
138. Do not add any additional grouping.
139. Sort the detail records by sale date.
140. Use the Stepped layout in Portrait orientation.
141. Name the reportNewFlavorRptand then view the report to check your work.
142. Switch to Layout view and add more grouping and totals to theNewFlavorRptreport.
143. Display theGroup, Sort, and Totalpane and add new grouping by values in theSaleDatefield byMonth.
144. Add totals to each group to calculate theSumof values in theTotalSalefield.
145. Save and close the report.
146. Create a new report from scratch in Layout view.
147. From theSalesArchivetable, add theSaleDatefield to the report. Add these fields in order to the right of theSaleDatecontrols:ItemNamefrom theItemstable, andQuantityandTotalSalefrom theSalesArchivetable.
148. Resize theItemNamecontrols so all the item names are visible. (Hint: Widen theItemNamecolumn so the entireSea Salt and Caramelname is visible.)
149. Add theItemIDfield from theItemstable. Move theItemIDcontrols so they appear to the left of theItemNamecontrols.
150. Add the current date to the report header. Use this date format:Thursday, February 25, 2019.Do not include the time.
151. Add page numbers centered in the report footer. Use this page number format:Page N of M
152. Save the report as:SalesArchiveRpt