CIS 310 Assignment #2
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:
- Complete the data types that have been left blank.
- Make a primary key for each table.
- Add a new field to the Products Table called Notes in which product descriptions can be given.
- Add an additional field to the Warehouse Table which can handle pictures of the warehouse buildings (data type of Ole Object).
- 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
1003
1004
2001
2002
2003
2004
2005 |
Office 2010
Adobe Professional
Adobe Acrobat
Mac Word 2010
Mac Office 2010
Office 2007
Quicken
MS Word 2010
Internet Explorer |
495.00
395.50
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
30
40 |
Marty Smith
Bill Allen
Anna Carlin
your name |
616-555-2327
912-857-4385
909-869-3238
909-869-9999 |
Akron
Chicago
Pomona
Pomona |
OH 44301
IL 60601
OR 97086
CA 91768 |
| Inventory Table |
| Warehouse ID |
Product ID |
Inventory Qty |
10
30
10
40
20
20
40
30
10
30
10
40 |
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.
The resulting report should have the following information:
- The appropriate information
- A title that includes the report subject and your name
- The report preparation date
- Company logo
