A+ professional and original solution.

Problem:         BoarDome is in the business of producing skate, surf and snow boards.  They

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

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 

Description Text 25 Description of the Product 

On Hand Number Long 

Cost Currency   Cost of Product 

Selling Price Currency   Selling Price of Product 

Vendor Code Text 2 Code of Product Vendor, Yes (Duplicates OK) 

Duplicates) 

Number of Units On Hand 

Integer 

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 

Vendor Code Text 2 Vendor Code (Primary Key), Yes (No 

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 

Description 

Size 

Duplicates) 

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 

AR Artisan's 

MS Tshirts 4less 134 Union Delana SD 41345 605-555-3498 

WW glassworks 655 Clive Great 

Name Address City State Zip Code Telephone Number

printer 

3540 

Grand 

Hancock WI 69780 414-555-7865 

WV 34567 304-555-4532 

Falls 

 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 

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

Access Assignment #2   (25 Points)

Note:  Access Assignment #1 must be completed first before beginning 

Assignment #2.

Problem/Objectives of the Assignment:

The employees who staff the gift shop have determined a number of questions they want the 

database management system to answer.  You must obtain answers to the questions posed by 

the volunteers using Microsoft Access:  Create a series of queries to answer questions 

associated with the BoarDome Database.

Instructions:  Perform the following tasks after you open the database you created for 

Assignment One:

 Create a query for the Product Id, Description and Selling Price for all the records in the Product 

Table sorted by Product Id. Label ProductId Query.

Create Only a query for the Product Id, Description and Selling Price for all products with the 

Vendor Code of MS.  Label as MS Query.

Create a query for the Product Id, Description, and Vendor Code for all products that cost more 

than $20. List the records in descending order by Cost. Label as Cost Query.

Create a query for the Product Id and Description for all products that have a Selling Price of $5 

or less. Label as Selling Price Query.

 Create a query for all fields for those products that cost more than $10 and where the number 

of units on hand is less than 10. Label as Low Inventory Query.

 Create a query for all fields for those products that have a Vendor Code of WW or have a 

Selling Price less than $10. Label as WW-Vendor Code Query.

 Join the Product Table and the Vendor Table.  Create a query for the Product Id, Description, 

Cost, Name and Telephone Number fields.  Label the query as Product and Vendor Query.

 Restrict the records retrieved in Task 7 to only those products where the number of units on 

hand is less than 10 (Also show the Number of Units on Hand).  Label the query as 

Inventory Less than 10 Query.

Create a query for the Average Cost and Average Selling Price for all products. (Just two 

fields should be displayed). Label as Average Query.

 Select Product Id, Description, On Hand and Cost.  Compute the On Hand Value (On 

hand*cost) for all records in the table. Label as On Hand Query.  HINT: (Enter your 

formula in the blank field to the right of Cost).

 Create and save the following validation rules for the Product Table in Design view.

a.      Make the Description field a required field.

b.      Ensure that any lowercase letters entered in the Product Id field are converted to 

uppercase.

c.      Specify that the on hand units must be between 0 and 100. Include validation text.

SSave the changes.

Open the Product Form you created in Assignment #1, and add the following record to the Product 

table.

MN04              board game     4          $17.50             $21.94             WW