Music

Only available on StudyMode
  • Download(s) : 84
  • Published : January 10, 2013
Open Document
Text Preview
For this database project, you will use MS Access to create a database of vendors, and the related items they sell to your hardware store. For example, if you are Lowe’s® or Home Depot®, you purchase items to sell from various vendors, such as Black and Decker®. Some of those items may be power tools or hand tools. You may purchase items for sale from other vendors, such as garden tools. Your database is made up of two tables: * The vendor table,– it will contain at least four vendors in it. * And the item table,– it will contain at least three items for each vendor.

In this project, you will:

1. Design the Table Structure
2. Enter the Data. Print.
3. Relate the two tables. Print.
4. Then delete one Item record. Print
5. Create three Queries. Print.
6. Create one Report. Print.
7. In addition, you may choose to create yet another report for up to 5 points extra credit. Print.

1. DESIGN YOUR TABLE STRUCTURE.
You will do this for both tables. Please note the information below describing both tables. Your tables must include the information below. You must have the same field names, and each field must have the same data type, same field size and other information that must be defined in the field properties.

DATABASE NAME – When you save your database, you must save it as Lastname-Hardware_Store, ex. KING_Hardware_Store

VENDOR TABLE
Field Name| Data Type| Field Size| Other Information|
Vendor ID| Text| 5 Chars| Primary Key – may contain alpha and numeric. You create this, do not use autonumber.| Vendor Name| Text| 20 Chars| |
Vendor City| Text| 12 Chars| |
Vendor State| Text| 2 Chars| Must be uppercase|

ITEM TABLE
Field Name| Data Type| Field Size| Other Information|
Item ID| Auto-number| | Primary Key, this must be autonumber| Description| Text| 20 Chars| |
Vendor ID| Text| 5 Chars| For each record, the data element in this field must exist in the Vendor Table| List Price| Currency| | Leave decimal places as auto|
On-Hand Qty| Number| Long Integer| |
Item Type| Text| 4 Chars| Codes must be validated, and must be in upper case. The codes are TOOL, GRDN, ELEC, and OTHR. Default is OTHR.| On Sale| Yes / No| | |

Follow the instructions/skills learnt in the practice exercise to save your database, define its fields, and enter your data. Although the databases, tables, and fields are different, the procedures are the same as in the handout, and as described in class.

2. ENTER YOUR DATA

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 Vendor record may look like:
VENDOR ID| VID01|
VENDOR NAME| Black and Decker|
VENDOR CITY| Chicago|
VENDOR STATE| IL|

An example of what one Item record may look like:
ITEM ID| 1|
DESCRIPTION| Power Drill|
VENDOR ID| VID01|
LIST PRICE| $39.95|
ON-HAND QTY| 10|
ITEM TYPE| TOOL|
ON SALE| No|

Feel free to use the two records above as the first records in your Vendor and Item tables, respectively.

Your inventory items will be classified according to four different Item Types. Your items table must contain at least three records with each of the following Item Types: TOOL| Tools|
GRDN| Garden items|
ELEC| Electrical items|
OTHR| Other items|

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.

In addition, your Item Table must contain the following data combinations to test your criteria: * You must have at least two Item Type TOOL records, one that is On Sale, and one that is not On Sale * You must have at least two On Sale records...
tracking img