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.

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. 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

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme