For this database project, you will use Microsoft Access to create an inventory database for a hardware store, which contains more than 10 records in each table and with the fields listed below for each table.
In this project, you will:
• Design the Table Structures and Relate them
• Enter the Data
• Create Two Reports (one report has to be saved as a screen shot before creating the second report)
• Create Two Queries
Procedure:
- DESIGN YOUR TABLE STRUCTURES with the following field names: (10 Points)
Table 1 -
|
Field Name
|
Data Type
|
Width
|
Other Information
|
|
Item ID
|
Auto #
|
5 chars.
|
Primary Key – may only contain automated integers created by the database system.
|
|
Description
|
Text
|
20 chars.
|
|
|
Vendor ID
|
Number
|
6 Characters
|
6 digits in length
|
|
List Price
|
Currency
|
|
Leave decimal places as 2
|
|
On-Hand Qty
|
Number
|
|
Long Integer
|
|
Item Type
|
Text
|
4 chars.
|
Type Codes must be validated, and must be formatted in upper case. Codes are: SOFT, HARD, ELEC, or OTHR. Default value is OTHR
|
|
On Sale
|
Yes / No
|
|
Assume Sale Price is 25% off
|
Table 2
|
Field Name
|
Data Type
|
Width
|
Other Information
|
|
Customer ID
|
Auto #
|
5 chars.
|
Primary Key – may only contain automated integers created by the database system.
|
|
Description
|
Text
|
20 chars.
|
|
|
Customer Name
|
Memo
|
50 chars.
|
|
|
Customer Address
|
Memo
|
50 chars
|
|
|
Customer State
|
Text
|
2 Char
|
Use Postal Codes
|
|
Customer Zip
|
Number
|
Upto 9 char..
|
Postal Codes from US Postal Service
|
|
Phone Number
|
Number
|
10 chars
|
|
Table 3 – Sales Receipts
|
Field Name
|
Data Type
|
Width
|
Other Information
|
|
Receipt ID
|
Auto #
|
10 chars.
|
Primary Key – may only contain numbers and must be auto generated
|
|
Customer ID
|
Number
|
5 chars.
|
Secondary Key from other Table
|
|
Item ID
|
Number
|
5 chars.
|
Secondary Key from other Table
|
|
Quantity Purchased
|
Number
|
6 chars.
|
|
|
Sub-Total
|
Calculation / Curency
|
10 Chars
|
Calculate this from the data given make it Curency with 2 decimal places (Qty * Price)
|
|
Tax
|
Curency
|
8 Chars
|
Curency same as Total. Assume 9% for Tax Rate
|
|
Total
|
Curency
|
12 Chars.
|
Add Subtotal & Tax
|
Start Access and select “Blank Access Database.” Important: when prompted to “Save in”, name your database file “Inventory.” Then click on Create. Don’t try to go any further until this is done!
In the main database window, the Table button should be selected (embedded). Double-click on Create Table in Design View to set up the structure of your database file. The cursor will be at the 1st field. (note: you will create the Table in Design View, not using a Wizard or in Datasheet View).
When the Table Design Screen is displayed, being by typing in the first field name and then tab over to Data Type. Choose the field type from the arrow options (or you can just type the first letter of the type). Remember that Access uses “text,” “number,” “yes/no,” for what our book calls “character,” “numeric,” and “logical.”
At this point, you’ll see a General tab in the lower left corner of the screen, with many options, including field size. You must set up all field specifications here, as we did in class. After you have entered all the fields described in the table on Page 1 above, make the Item ID to be the primary key, using the key icon, as described in class. As you enter data in Section 2 below, using Datasheet View, you will have to be careful to insure that you do not have any duplicate Item IDs. When finished, save file.
2. ENTER YOUR DATA (10 points)
Enter your data directly into the table. You can make up fictitious data, but it must be reasonable, because we will be sorting and making queries from this data.
An example of what one record in tabloe 1 of data may look like:
· ITEM ID – Mouse
· DESCRIPTION – Computer Accessory
· VENDOR ID – 4501 (a number – you may want to number your vendors 1, 2, 3, etc. You may also want to have, say, all of one kind of item come from the same vendor id)
· LIST PRICE - $9.95
· ON-HAND QTY – 150
· ITEM TYPE – HARD
· ON SALE - NO
Your data must contain at least tweleve records and there must be one of each of the following Item Types:
· SOFT Software (Window XP, Microsoft Office, Adobe Acrobat, etc.)
· HARD Hardware (monitor, hard disk, CPU, etc.)
· ELEC Electrical (TV, VCR, DVD, MP3 player, battery charger, etc.)
· OTHR Other (pencil, pen, notepad, bulletin board, marker, etc.)
You must validate that the values keyed into Item Type are one of the four types listed above, and display an appropriate error message when something else is keyed in.
Combination of data required to test your Select query (Query 2) to prove that it works:
· You must have at least two Item Type HARD records, one that is On Sale, and one that is not On Sale
· You must have at least one On Sale record whose Item Type is not HARD.
3. REPORTS (15 points)
You will be required to create two reports, one before and one after you have deleted one record from your Inventory table. Remember, you will be deleting a record from the table, not the report (you cannot delete a record from a report). The report just prints what is in the table. Thus one report should have 10 or more records on it, and the second report should have one less record on it.
Using the Report Wizard, create a Report on your data. Choose all fields except On Sale Item, using the arrow >. Do not do any grouping, but sort the records descending by Item ID. Choose Tabular and Landscape. Choose any print style you like. For the Title, use All Inventory Items Report. Now print the Report. Please bring this report hard copy to the lab.
Go back into datasheet view of the Table, and delete one of the records from the table, as shown in class. Then make another report, using the same process as the first one. For the title of this one, use One Item Deleted Report.
4. QUERYs (15 points)
First Query
· Using Design View, create a query on your data. Select the Item ID, Description, Item Type, and On-Hand Quantity of all items. Sort the records descending by Item ID.
· Click on the save icon, and save it as Query 1.
· This query will not contain all the fields in the table, but it will contain all of the records.
Second Query
· Create another query, using Design View, selecting the following fields: Item ID, Description, Item Type, List Price, and On Sale.
· The query (using the Criteria row) should ONLY include rows that are both On Sale and Item Type HARD. In addition, do not show the On Sale field.
· There is no need to sort this query.
· Click on the save icon, and save it as Query 2.