caGO19_AC_CH01_GRADER_1E_HW – Kiosk Inventory 1.0

Project Description:

In this project, you will create database objects to track the inventory of items for sale in a kiosk located in a college snack bar. You will create a table and import data from Excel to create a second table. You will create a simple query, a form, and a report.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Access. Open the file Student_Access_1E_Kiosk_Inventory.accdb   downloaded with this project. Create a new table in Datasheet view.

0

2

Beginning   in the second column of the table and using the specified data types, create   the following fields (in this order):
 

Item   (Short Text), Category   (Short Text), Campus   (Short Text), Storage   Location (Short Text), Price (Currency), Quantity in Stock (Number).

12

3

Change the data type of the ID   field to Short Text, rename the ID field to Item ID, and change the field   size to 10   Save the table as 1E Inventory.

4

4

Add the   following three records to the 1E Inventory table and then close the table.

Item ID

Item

Category

Campus

Storage Location

Price

Quantity in Stock

C-1

Chocolate Bar

Candy

Southeast

SE100A

.89

250

C-2

Lollipop

Candy

Southeast

SE100A

.5

500

T-1

T-shirt

Clothing

Southeast

SE100B

17.5

100

10.5

5

Append the records from the   downloaded Excel file a01E_Inventory.xlsx   to the 1E Inventory table and then open the table in Datasheet view (the   table has 17 records).

10

6

Switch to   Design view and delete the Campus field. For the Category field, enter a   description of Enter the category of the item and then change the field   size to 25.   Save the table.

8

7

Switch to Datasheet view, apply   Best Fit to all of the fields in the table, save the table, and then close   the table.

0

8

Import   the records from the downloaded Excel file a01E_Inventory_Storage.xlsx into the database as a new table   named 1E   Inventory Storage. Designate the first row as column headings and   the Category field as the primary key. Open the Inventory Storage table in   Datasheet view (the table has five records).

12

9

Switch to Design view. For the   Location Detail field, change the field size to 35, enter a description of Room and bin   number or alternate location of inventory item, and then save the   table.

4

10

Switch to   Datasheet view, apply Best Fit to all of the fields in the table, save the   table, and then close the table.

0

11

Based on your 1E Inventory   table, use the Query Wizard to create a simple query. Include only the three   fields that will answer the question, For   all items, what is the storage location and quantity in stock?. Keep the   default name of 1E Inventory Query, click Finish to   display the query results, and then close the query.

10

12

Based on   your 1E Inventory table, use the Form tool to create a form for the table.   Save the form as 1E Inventory Form, display the form in Form   view, and then close the form.

8.5

13

Based on your 1E Inventory   table, use the Report tool to create a report. Delete the Category and Price   fields from the report. Save the report as 1E Inventory Report.

13

14

Sort the   Item ID field in ascending order. Set the width of the Item ID field to 0.75   inch. Set the width of the Storage Location field to 1.5 inches. Delete the page   number from the report, save the report, and then close the report.

8

15

Save and close the database,   and then submit for grading.

0

Total Points

100