you will work with a sales database from Top’t Corn, a popcorn company with multiple food trucks and one store in a local shopping mall.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Access 2016 capstone project AC-1 

Working with a Sales Database 

In this project, you will work with a sales database from Top’t Corn, a popcorn company with multiple food trucks and one store in a local shopping mall. Previously, Top's Corn kept their data in multiple Excel workbooks. Recently, they decided to expand their product offerings at different price points, and they realized they needed a more robust database to track sales. You will help them create a new database table and clean up data imported from Excel.

You will begin by creating new tables to track sales and sale details. You will use the Form Wizard to create a form based on the new tables. Next, you will modify the existing Items table and create a form based on that table. You will create a new form from scratch in Layout view to display records from the Locations table. Next, you will clean up the imported data in the Sales_Archive table and create a relationship between the data in the Sales_Archive and the Items tables. You will create a series of queries using a variety of criteria. Finally, you will create a report using the Report Wizard.

Skills needed to complete this project:

• Create a new record in a table
• Apply date formatting to a field by modifying the field Format property
• Adjust table column widths
• Create and save a new table
• Add a new field to a table
• Create a lookup field using values from another table
• Set a default value for a field in a table
• Use the Form Wizard to create a new form
• Change the data type of a field
• Create a Single Record form based on a table
• Create a new blank form in Layout view
• Add fields to a blank form from Layout view

 

• Resize controls in a form
• Delete a field from a table
• Delete a record from a table
• Find and replace data in a table
• Rename a field in a table
• Create a one-to-many relationship between two tables
• Enforce referential integrity in a one-to-many relationship
• Create a simple select query to combine fields from multiple tables
• Add text criteria to a query
• Add numeric criteria to a query
• Use the Report Wizard to create a new report

1. Open the start file AC2016-Capstone-Level1. NOTE: If necessary, enable active content by clicking the Enable Content button in the Message
2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save
3. Open the Sales table and add three records to the table with the following (Hint: The first field in the table, SaleID, is an AutoNumber field, so there is nothing to enter for each record.)

 

SaleDate	SaleLocation	PaymentType
11/1/2016	K Street	Credit Card
11/1/2016	George Washington University	Cash
11/1/2016	Georgetown	Credit Card

 

4. Switch to Design view and modify field
   1. Apply the Long Date format to the SaleDate
   2. Switch back to Datasheet view and adjust the width of the SaleDate field so the entire long date is visible. Save the table when
   3. Close the table. Save the table when
5. Create a new table to capture the details for each
   1. The first field should be an AutoNumber field named: SaleDetailID
   2. The second field should be a lookup field named: SaleID The lookup field should be limited to values in the SaleID field of the Sales Include only the SaleID in the lookup field. Enable data integrity by restricting deletions.
   3. Save the table as: SaleDetails
   4. Add a third field to the far right of the table. Name this field: Item Include all the fields from the Items Sort the lookup items by values in the ItemName field. Hide the primary key field. Enable data integrity by restricting deletions.
   5. Add a Number field to the right of the Item Name the field: Quantity
   6. Set the default value for the Quantity field to: 4
   7. Add three records to the table with the following (Hint: Remember, the first field in the table, SaleDetailID, is an AutoNumber field, so there is nothing to enter for each record.)

 

SaleID	Item	Quantity
1	Chocolate	4
1	Old Bay	2
2	Truffle	1

 

1. Close the

6. Use the Form Wizard to create a new form for inputting sales
   1. Include all the fields from the Sales
   2. Include the Item and Quantity fields from the SaleDetails
   3. View the form data by records in the Sales table with related records in the SaleDetails table displayed in a subform 
   4. The subform should be displayed as a Datasheet.
   5. Name the main form: SalesForm and name the subform: SaleDetailsSubform (Hint: Be sure to remove the space between SaleDetails and Subform in the subform name suggested by )
   6. Open the form in Form view to review your
   7. Navigate to the record in the main form for SaleID 3 and enter sale details in the subform as follows:

Item: Truffle, Quantity: 4

Item: Old Bay, Quantity: 2

              8. Close the form.

7.Open the Items table and modify the table fields as follows:

1. 1. Set the Default Value property for the Price field to: 4
   2. Change the data type for the Price field to: Currency
   3. Autofit the width of the ItemName
   4. Save the changes and close the Items

8. Create a Single Record form using the Items table as the record source. Save the form with the name ItemsSingleRecordForm and then close the form.

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of