Access Assignment - BoarDome Database
Problem: BoarDome is in the business of producing skate, surf and snow boards. They run a small museum and gift shop, located in airports in cities where the extreme sports are big. The gift shop purchases products from vendors for products and merchandise with the company name on them. The director of the gift shop has asked you to create and update a database that employees can use. The database consists of two tables; The Product table contains information on items available for sale. The Vendor table contains information on the vendors.
Objectives of the Assignment:
Using Microsoft Access:
1. Create a new database in which to store all the objects related to the gift shop system data.
2. Create tables and an input form
3. Populate the tables using data supplied
4. Perform file maintenance to tables
5. Generate a report utilizing the data
Instructions: Perform the following tasks.
1. Create a new database called BoarDome Gift shop Database.
2. Create a Product Table using the structure shown below. Use the nameProduct for the table name and save it with a File name = Product - Your Name.
|
Field Name
|
Data Type
|
Field Size
|
Description
|
|
Product Id
|
Text
|
4
|
User Id Number, Primary key, Yes (No Duplicates)
|
|
Description
|
Text
|
25
|
Description of the Product
|
|
On Hand
|
Number
|
Long Integer
|
Number of Units On Hand
|
|
Cost
|
Currency
|
|
Cost of Product
|
|
Selling Price
|
Currency
|
|
Selling Price of Product
|
|
Vendor Code
|
Text
|
2
|
Code of Product Vendor, Yes (Duplicates OK)
|
3. Add the data shown below to the Product Table.
|
Product Id
|
Description
|
On Hand
|
Cost
|
Selling Price
|
Vendor Code
|
|
CH04
|
hoodies
|
11
|
$26.75
|
$28.90
|
WW
|
|
DI24
|
lanyards
|
14
|
$3.75
|
$4.95
|
MS
|
|
GL18
|
hats
|
2
|
$27.50
|
$29.95
|
MS
|
|
JG01
|
glasses
|
3
|
$5.40
|
$6.95
|
MS
|
|
PC03
|
mugs
|
5
|
$8.50
|
$10.95
|
WW
|
|
ST23
|
Stationery
|
8
|
$3.95
|
$5.00
|
AR
|
|
TD05
|
t-shirts
|
6
|
$13.75
|
$15.95
|
WW
|
|
WI10
|
shorts
|
10
|
$7.50
|
$9.95
|
MS
|
|
WL34
|
Can holders
|
15
|
$2.50
|
$2.95
|
AR
|
|
YO12
|
stickers
|
9
|
$1.60
|
$1.95
|
WW
|
4. Create a Vendor Table using the structure shown below. Use the nameVendor for the table and save it with a File name = Vendor - Your Name.
|
Field Name
|
Data Type
|
Field Size
|
Description
|
|
Vendor Code
|
Text
|
2
|
Vendor Code (Primary Key), Yes (No Duplicates)
|
|
Name
|
Text
|
20
|
Name of Vendor
|
|
Address
|
Text
|
15
|
Street Address
|
|
City
|
Text
|
15
|
City
|
|
State
|
Text
|
2
|
State (Two-Character Version)
|
|
Zip Code
|
Text
|
5
|
Zip Code (Five-Character Version)
|
|
Telephone Number
|
Text
|
20
|
Telephone Number (999-999-9999 Version
Note: Use the Phone NumberInput Mask format
|
5. Add the data shown below to the Vendor Table.
| Vendor Code |
Name |
Address |
City |
State |
Zip Code |
| AR |
Artisan's printer |
3540 Grand |
Hancock |
WI |
69780 |
| MS |
Tshirts 4less |
134 Union |
Delana |
SD |
41345 |
| WW |
glassworks |
655 Clive |
Great Falls |
WV |
34567 |
6. Create a form for the Product Table. Use AutoForm Wizard to create your form. Save the form as "Product - Your Name".
7. Use the form you created to add the following two new products to the Product Table.
|
Product Id
|
Description
|
On Hand
|
Cost
|
Selling Price
|
Vendor Code
|
|
CH08
|
Key Chains
|
12
|
$20.00
|
$25.00
|
AR
|
|
PS30
|
Posters
|
20
|
$3.50
|
$4.50
|
MS
|
8. Create a report. Report should contain the following elements: Product ID,Description, On Hand, and Cost.
· Use Report Wizard to create the report
· Sort Order for your records = On Hand (Descending Order)
· Layout = Tabular; Orientation = Portrait; Style = Concourse or any other style
· Title = Inventory Report - Your Name.
Output Requirements:
· Save to your folder on the your pdrive or hard drive:
1. Created database entitled, BoarDome Gift shop Database One.
2. Two created tables entitled, Product and Vendor
3. One created form entitled, Product
4. One created Report entitled, Inventory Report.