2 assignments for Microsoft Access

MIS 204

Access Assignment #1

(Northern Lakes Brewery Database)

(20 Points)

Problem: Northern Lakes Breweries runs a small museum and gift shop, located in airports in cities where their beer is served in the local bars and pubs. 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 Northern Lakes Breweries Gift shop Database.

2. Create a Product Table using the structure shown below. Use the name Product for the table name and save it with a File name = Product – Your Name.

Field Name	Data Type	Field Size	Description
Product Id	Short Text	4	User Id Number, Primary key, Yes (No Duplicates)
Description	Short 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	Short Text	2	Code of Product Vendor, Yes (Duplicates OK)
Image	OLE

3. Add the data shown below to the Product Table.

Product Id	Description	On Hand	Cost	Selling Price	Vendor Code	Image
CH04	Hoodies	11	$26.75	$28.90	WW
DI24	Shot glasses	14	$3.75	$4.95	MS
GL18	Hats	2	$27.50	$29.95	MS	Hat picture
JG01	Sun glasses	3	$5.40	$6.95	MS
PC03	Beer mugs	5	$8.50	$10.95	WW	Beer mug picture
ST23	Collectible bottles	8	$3.95	$5.00	AR	Bottle picture
TD05	T-shirts	6	$13.75	$15.95	WW	t-shirt picture
WI10	Socks	10	$7.50	$9.95	MS
WL34	Can holders	15	$2.50	$2.95	AR
YO12	Can openers	9	$1.60	$1.95	WW

4. Create a Vendor Table using the structure shown below. Use the name Vendor for the table and save it with a File name = Vendor – Your Name.

Field Name	Data Type	Field Size	Description
Vendor Code	Short Text	2	Vendor Code (Primary Key), Yes (No Duplicates)
Name	Short Text	20	Name of Vendor
Address	Short Text	15	Street Address
City	Short Text	15	City
State	Short Text	2	State (Two-Character Version)
Zip Code	Short Text	5	Zip Code (Five-Character Version)
Telephone Number	Short Text	20	Telephone Number (999-999-9999 Version Note: Use the Phone Number Input Mask format

5. Add the data shown below to the Vendor Table.

Vendor Code	Name	Address	City	State	Zip Code	Telephone Number
AR	Artisan's printer	3540 Grand	Hancock	WI	69780	414-555-7865
MS	T-shirts 4less	134 Union	Delana	SD	41345	605-555-3498
WW	glassworks	655 Clive	Great Falls	WV	34567	304-555-4532

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 report

· Sort Order for your records = On Hand (Descending Order)

· Layout = Tabular; Orientation = Portrait; Style = Module

· Add the company logo by editing with

· Title = Inventory Report - Your Name.

Output Requirements:

· Save to your folder on the courseware data disk then upload the Canvas folder:

1. Created database entitled, Northern Lakes Breweries Gift shop Database One.

2. Two created tables entitled, Product and Vendor

3. One created form entitled, Product

4. Printed report entitled, Inventory Report.

Images