Create Access Database
Assignment 2 v2 shell.accdb
| ProductID | ProductName | UnitCostPrice | UnitSalesPrice |
|---|---|---|---|
| 1 | Merlot | ||
| 2 | Blanc | ||
| 3 | Vodka | ||
| 4 | Gin and Tonic | ||
| 5 | Whisky | ||
| 6 | Brandy | ||
| 7 | Victoria | ||
| 8 | Samuel Adams |
Data and instructions for Assignment 2 v2.xlsx
Instructions
| 1 | What to do with the Product sheet? | ||
| The ProductTable is already created for you in the Access database shell file. You only need to do the following: | |||
| 2.1 | Open the table in Design view. Look at the Description of each field and set up the field properties accordingly | ||
| NOTE: Be careful to change the data types for the fields correctly according to the Description | |||
| 2.2 | Change to Data view | ||
| 2.3 | Enter the data given in the Product sheet of this Excel file into the table | ||
| 2 | What to do with the Location sheet? | ||
| Create a Location table in the database with the fields as follows: | |||
| LocationID | primary key, autonumber | ||
| LocationRef | short text, 30 characters, required | ||
| Description | short text, 100 characters | ||
| Populate the table with data given on Location sheet of this Excel file. | |||
| 3 | What to do with the Sales sheet? | ||
| Import data in the Sales sheet into a new Sales table. Make sure all field properties are correctly and reasonably set up. | |||
| Make sure there is a primary key for the Sales table that is of type Autonumber. | |||
| Make sure all field properties are correctly and reasonably set up. | |||
| Make sure all foreign keys (FK) have data types that match their corresponding primary keys | |||
| Create lookup list for the ProductID_FK field so that it displays (and can take only) the corresponding ProductNames from the Product table in a drop down list. Sort ProductNames in ascending order in the drop down list. Hide the ProductID_FK field in the drop down list | |||
| Establish validation rule (and corresponding validation text) for field SalesDate: SalesDate must not be greater than today's date. | |||
| 4 | Set up relationships in the 3 tables correctly. (Hint: work out relationships between 2 pairs of tables: Location and Product, and Product and Sales) | ||
| 5 | Create a form with a subform to display Locations and the sales records for each location | ||
| 6 | Create the following queries | ||
| 6.1 | A query to display all sales at location CG | ||
| 6.2 | A query to calculate and display revenue (sales) and cost for all sales records in the Sales table. Include Product name, NoOfItemsSold, SalesDate and LocationRef in the query result. Sort the result in ascending order of Product name. | ||
| 6.3 | A query to display top sales with revenue greater than $80 and bottom sales with revenue smaller than $40 on the 2nd March 2018 | ||
| 6.4 | A query to display all products on CB level | ||
Location
| LocationID | LocationRef | Description |
| 1 | CG | Cabinet on ground floor |
| 2 | C2-S | Cabinet on 2nd floor near stairs |
| 3 | C2-R | Cabinet on 2nd floor at rear |
| 4 | CB-S | Cabinet in basement near stairs |
| 5 | CB-R | Cabinet in basement at rear |
Product
| ProductID | ProductName | UnitCostPrice | UnitSalesPrice |
| 1 | Merlot | $ 7.00 | $ 14.00 |
| 2 | Blanc | $ 7.00 | $ 14.00 |
| 3 | Vodka | $ 12.00 | $ 24.00 |
| 4 | Gin and Tonic | $ 10.00 | $ 20.00 |
| 5 | Whisky | $ 15.00 | $ 30.00 |
| 6 | Brandy | $ 10.00 | $ 20.00 |
| 7 | Victoria | $ 4.00 | $ 8.00 |
| 8 | Samuel Adams | $ 5.00 | $ 10.00 |
Sales
| ProductID_FK | NoOfItemsSold | LocationID_FK | SalesDate |
| 1 | 3 | 1 | Thu 1/03/18 |
| 2 | 2 | 1 | Thu 1/03/18 |
| 4 | 10 | 1 | Thu 1/03/18 |
| 7 | 5 | 1 | Thu 1/03/18 |
| 5 | 4 | 1 | Thu 1/03/18 |
| 3 | 6 | 1 | Thu 1/03/18 |
| 6 | 7 | 1 | Thu 1/03/18 |
| 8 | 8 | 1 | Thu 1/03/18 |
| 1 | 7 | 3 | Thu 1/03/18 |
| 2 | 8 | 3 | Thu 1/03/18 |
| 4 | 8 | 3 | Thu 1/03/18 |
| 7 | 7 | 3 | Thu 1/03/18 |
| 5 | 2 | 3 | Thu 1/03/18 |
| 3 | 3 | 3 | Thu 1/03/18 |
| 6 | 3 | 3 | Thu 1/03/18 |
| 8 | 8 | 3 | Thu 1/03/18 |
| 1 | 8 | 1 | Fri 2/03/18 |
| 2 | 3 | 1 | Fri 2/03/18 |
| 4 | 2 | 1 | Fri 2/03/18 |
| 7 | 8 | 1 | Fri 2/03/18 |
| 5 | 8 | 1 | Fri 2/03/18 |
| 3 | 5 | 1 | Fri 2/03/18 |
| 6 | 2 | 1 | Fri 2/03/18 |
| 8 | 5 | 1 | Fri 2/03/18 |
| 1 | 1 | 3 | Fri 2/03/18 |
| 2 | 6 | 3 | Fri 2/03/18 |
| 4 | 5 | 3 | Fri 2/03/18 |
| 7 | 3 | 3 | Fri 2/03/18 |
| 5 | 1 | 3 | Fri 2/03/18 |
| 3 | 5 | 3 | Fri 2/03/18 |
| 6 | 5 | 3 | Fri 2/03/18 |
| 8 | 10 | 3 | Fri 2/03/18 |
| 1 | 3 | 2 | Sat 3/03/18 |
| 2 | 7 | 2 | Sat 3/03/18 |
| 4 | 10 | 2 | Sat 3/03/18 |
| 7 | 3 | 2 | Sat 3/03/18 |
| 5 | 2 | 2 | Sat 3/03/18 |
| 3 | 8 | 2 | Sat 3/03/18 |
| 6 | 3 | 2 | Sat 3/03/18 |
| 8 | 10 | 2 | Sat 3/03/18 |
| 1 | 1 | 4 | Sat 3/03/18 |
| 2 | 3 | 4 | Sat 3/03/18 |
| 4 | 5 | 4 | Sat 3/03/18 |
| 7 | 8 | 4 | Sat 3/03/18 |
| 5 | 8 | 4 | Sat 3/03/18 |
| 3 | 3 | 4 | Sat 3/03/18 |
| 6 | 1 | 4 | Sat 3/03/18 |
| 8 | 6 | 2 | Sat 3/03/18 |
| 1 | 11 | 2 | Sun 4/03/18 |
| 2 | 11 | 2 | Sun 4/03/18 |
| 4 | 3 | 2 | Sun 4/03/18 |
| 7 | 4 | 2 | Sun 4/03/18 |
| 5 | 5 | 2 | Sun 4/03/18 |
| 3 | 2 | 2 | Sun 4/03/18 |
| 6 | 8 | 2 | Sun 4/03/18 |
| 8 | 2 | 2 | Sun 4/03/18 |
| 1 | 9 | 4 | Sun 4/03/18 |
| 2 | 1 | 4 | Sun 4/03/18 |
| 4 | 10 | 4 | Sun 4/03/18 |
| 7 | 3 | 4 | Sun 4/03/18 |
| 5 | 10 | 4 | Sun 4/03/18 |
| 3 | 12 | 4 | Sun 4/03/18 |
| 6 | 10 | 4 | Sun 4/03/18 |
| 8 | 8 | 4 | Sun 4/03/18 |
| 1 | 7 | 1 | Mon 5/03/18 |
| 2 | 4 | 1 | Mon 5/03/18 |
| 4 | 5 | 1 | Mon 5/03/18 |
| 7 | 9 | 1 | Mon 5/03/18 |
| 5 | 7 | 1 | Mon 5/03/18 |
| 3 | 11 | 1 | Mon 5/03/18 |
| 6 | 2 | 1 | Mon 5/03/18 |
| 8 | 11 | 1 | Mon 5/03/18 |
| 1 | 3 | 5 | Mon 5/03/18 |
| 2 | 1 | 5 | Mon 5/03/18 |
| 4 | 10 | 5 | Mon 5/03/18 |
| 7 | 12 | 5 | Mon 5/03/18 |
| 5 | 5 | 5 | Mon 5/03/18 |
| 3 | 10 | 5 | Mon 5/03/18 |
| 6 | 12 | 5 | Mon 5/03/18 |
| 8 | 10 | 5 | Mon 5/03/18 |
| 1 | 6 | 2 | Tue 6/03/18 |
| 2 | 7 | 2 | Tue 6/03/18 |
| 4 | 4 | 2 | Tue 6/03/18 |
| 7 | 7 | 2 | Tue 6/03/18 |
| 5 | 5 | 2 | Tue 6/03/18 |
| 3 | 1 | 2 | Tue 6/03/18 |
| 6 | 2 | 2 | Tue 6/03/18 |
| 8 | 8 | 2 | Tue 6/03/18 |
| 1 | 1 | 3 | Tue 6/03/18 |
| 2 | 2 | 3 | Tue 6/03/18 |
| 4 | 8 | 3 | Tue 6/03/18 |
| 7 | 2 | 3 | Tue 6/03/18 |
| 5 | 4 | 3 | Tue 6/03/18 |
| 3 | 4 | 3 | Tue 6/03/18 |
| 6 | 2 | 3 | Tue 6/03/18 |
| 8 | 7 | 3 | Tue 6/03/18 |
| 1 | 2 | 5 | Wed 7/03/18 |
| 2 | 4 | 5 | Wed 7/03/18 |
| 4 | 6 | 5 | Wed 7/03/18 |
| 7 | 8 | 5 | Wed 7/03/18 |
| 5 | 1 | 5 | Wed 7/03/18 |
| 3 | 1 | 5 | Wed 7/03/18 |
| 6 | 6 | 5 | Wed 7/03/18 |
| 8 | 1 | 5 | Wed 7/03/18 |
| 1 | 1 | 4 | Wed 7/03/18 |
| 2 | 3 | 4 | Wed 7/03/18 |
| 4 | 3 | 4 | Wed 7/03/18 |
| 7 | 1 | 4 | Wed 7/03/18 |
| 5 | 0 | 4 | Wed 7/03/18 |
| 3 | 5 | 4 | Wed 7/03/18 |
| 6 | 3 | 4 | Wed 7/03/18 |
| 8 | 5 | 4 | Wed 7/03/18 |