need in8 hours no no extension

coolstudent1

microsoft_access.pdf

Home >Computer Science homework help >need in8 hours no no extension

Microsoft Access

This individual project is designed to give you a better understanding of how data can be organized

into a relational database structure and then used to provide answers to management queries and

information for reports. Software to be used is Microsoft Access 2010 or 2013. You will upload your

Access database in Blackboard. Do not email the assignment deliverables to me, post them on the

discussion boards or upload into your Content Collection.

Section 1 - Creating the Database Table Structure (10

points)

You will create three database tables for your firm. These tables should define the products you

sell and the warehouses (distributors) from which you order. They also should provide a way to

identify how much of each product is held in inventory in each warehouse. Use the database

structure shown below, which was created for a software mail order company.

Tables Data Fields Data Types

Products = Product ID Description

Unit Price

Number (long integer) Text (20)

Warehouse = Warehouse ID

Contact Phone

City

State Zip Code

Number (long integer)

Text (15)

Text (2)

Inventory = Warehouse ID

Product ID Inventory Qty

Number (long integer)

In this section, you are to:

1. Complete the data types that have been left blank.

2. Make a primary key for each table.

3. Add a new field to the Products Table called Notes in which product descriptions can be

given.

4. Add an additional field to the Warehouse Table which can handle pictures of the warehouse

buildings (data type of Ole Object).

5. Before you enter data into your tables, set up relationships between the primary and

foreign keys (if any) in your three files. Make sure that the two fields you are connecting

have the same data type (long integer). Select "enforce referential integrity" for each

relationship. Decide whether relationships are one-to-one or one-to-many.

Section 2 - Inserting Data into the Database Table

(10 points)

In this section you are to put the data that you have researched into the database tables.

Products Table

Product ID Description Unit Price

1001

1002

Office 2010

Adobe Professional

495.00

395.50

1003 1004

2001

2002 2003

2004

2005

Adobe Acrobat Mac Word 2010

Mac Office 2010

Office 2007 Quicken

MS Word 2010

Internet Explorer

425.95 395.00

195.50

298.99 45.00

345.00

49.99

Warehouse Table

Warehouse ID Contact Phone City State Zip

10 20

Marty Smith Bill Allen

Anna Carlin

your name

616-555-2327 912-857-4385

909-869-3238

909-869-9999

Akron Chicago

Pomona

OH 44301 IL 60601

OR 97086

CA 91768

Inventory Table

Warehouse ID Product ID Inventory Qty

10 30

40 20

40 30

30 10

2001 2003

2004

1003 2004

1001

1002 1002

1001

2001 2005

2005

10 50

120

60 200

180

60 240

110

80 190

145

Section 3 - Forms Design (15 points)

It is possible to enter data values directly into the table in Open view. What might be more

appropriate for sales people is to enter data by means of a form. Develop a form for the

Warehouse table and show the picture of the appropriate warehouse on the form. You only need to

have one actual web picture inserted on the form. (Remember to save a graphic from the web,

click with the right mouse button on the graphic, and then choose "save image as" and save the

image to your desktop. In order to insert it into a field in an Access Database, you will have to

open the .gif file in Paint or Photoshop and save it as a .bmp file. Access will then let you insert the

image as a (bitmapped) object into a field with data type "Ole Object".

Section 4 - Management Ad Hoc Query--One Table

(15 points)

The manager of the Mail-Order Software Company is concerned that the software is getting too

expensive, which will cause a decrease in demand for the products. The manager wants you to

query the database to determine which products have a unit price greater than $295. He wants

those products sorted from highest to lowest in price.

For this section, you are to create the query to answer the above question. The results for

thequery that shows only the following fields: Product ID, Description, and Price. Sort these items

in descending order from highest to lowest price (Note that the example above does not sort by

price, but by product ID). You will need to sort by PRICE from HIGHEST to LOWEST!

Section 5 - Ad Hoc Query--Multiple Tables (25 points)

A company sales person in the software mail order firm has just called in from a client's office

requesting information to close a deal. He wants an immediate verification that we have 35 copies

of Office 2010 that can be shipped from Warehouse 10.

Your task is to restate the sales person's request as a management query (using multiple tables).

Section 6 - Management Report (25 points)

A manager in the corporate office needs to know what the present dollar stock value is of each of

the items in each warehouse. In addition, she would like to know the overall grand total dollar

value of the entire inventory. Prepare an executive report displaying the answers to these

questions. Be sure the report has the current date on which it was prepared, and that your name

appears in the report header. In addition, prepare a logo in the report header that can come from

clipart, a Web .gif image (converted to a .bmp image), or from Paintbrush or Photoshop.

Hint. Probably the quickest way to do this section is to first design the query that displays the

correct data. They you can use the "group totals" option in the Report Wizard to prepare most of

the report. Lastly, you can go into design view and refine or add what you need, using the toolbox.