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.C Programming

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

 

  1. 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
  2. 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
  1. 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. 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:

Instructions Files

C Programming Experts

expert
Arapera Billing
C Programming

89 Answers

expert
Simon M.
C Programming

44 Answers

View More Experts
Disclaimer

The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.

Get Free Quote!

264 Experts Online