Ms Access Project 2

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 will be made up of two tables : The vendor table, which is the parent – it will contain at least five vendors in it. And the item table, which is the child – it will contain at least three items for each vendor. In this project, you will : Design the Table Structure Enter the Data Relate the two tables. Then delete one Item record. Create thee Queries. These queries do not have to be physically printed. Create one Report. The report does not have to be physically printed. In addition, you may choose to create yet another report for up to 5 points extra credit. This too does not have to be physically printed.

1. DESIGN YOUR TABLE STRUCTURE.

You will do this for both tables. You will do this without using a wizard, as demonstrated in class. 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 Hardware-Store

Follow the instructions given in the handout 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 who’s Item Type is not TOOL

3. RELATING THE TABLES

Now you must relate the two tables to each other. You are going to follow the procedure described in class (TOOLS drop-down menu, RELATIONSHIPS command) to relate the parent Vendor table to the child Item table. They will be related via the Vendor Table’s VENDOR ID to the Item Table’s VENDOR ID. Before you create your relationship, you must be sure that at least one record match exists between the two tables. When you create your relationship, you must be sure to specify that you will be enforcing referential integrity.
After you have created your relationship, and have entered all of your data (you may have entered all of your data before creating the relationship – that’s OK), you will need to delete one record, as demonstrated in class. You will need to delete one record from the Item Table (not the Vendor Table). You should delete a record that is not the last record in your table. If you have 17 records, don’t delete the 17th record. By looking at the numbered Item ID s, I’ll be able to tell that you successfully deleted one record.

4. QUERIES

Joined Query
Using Design View, create a joined query on your data. When you create your query, add both tables to the query. As in class, the relationship should be displayed in the Query by Example grid. Select from the Vendor Table: Vendor ID Vendor Name Select from the Item Table: Item ID Item Description List Price On-Hand Qty Item Type Sort the query (click in the sort fields in the definition grid) descending by Vendor Name (from the Vendor Table), then ascending by Item ID (from the Item Table) Run the query to make sure it displays the data correctly Save the query as Joined Query and then exit.

5. REPORTS

Using the Report Wizard, create a report on your ITEM TABLE data. Choose all fields except On Sale Item, using the arrow >. Group the items by Item Type. Then sort the records in descending order by Item ID. Choose Align Left 1 or Align Left 2 and Landscape. Choose any print style you like. For the Title, use Inventory Items Report.

To Download Click Here > Ms Access Project 2.pdf