Access Design and SQL project:

There are three parts to this project:

1. Develop and populate a database table using data appropriate to the queries you will be asked to make.

2. Query the database using SQL

3. Develop a form and a report using the wizard.

Database: You need to design and setup a database to hold the following information. You need to think about the type and length that you choose. If you are a analyst coming up with a database table you have to analyze the data to determine how big a field needs to be and what type you need to designate. For example, in determining how many characters you need in city, you should think of all of the local cities that you will need to include in the database. You should also determine which field should be designated as the key. Remember the key should define a record uniquely. That means if you query for a particular key, one and only one record should come up.

I will be looking for good design features and checking that you are not using special characters or embedded spaces in your field names. Embedded spaces means that you should not name a field street address (there is an embedded space between street and address), instead you should name the field streetAddress or street_address.

The database table you are going to design is a simplified inventory file for a store. You determine what you are selling. You need to carry the following information:

• item number

• item name

• category code (for clothes this might mean W for women, M for men, I for infants etc)

• years carried (this is how many years you have carried this product in your inventory)

• on hand

• on order

• reorder point (this means that when you inventory falls below this point, you need to order)

• price (you need to include decimals so use either single or double)

• cost (you need to include decimals)

You should enter your own data. Be sure to enter data appropriate to getting results that test the queries listed below. Create 10 records.

Queries: The following queries must be written using SQL (see SQL presentation for help). I do not want the extra code that is developed when Access converts the query from the interface into SQL.

1. Query for all items that have a particular category code AND have a price greater than a certain price. Show the item number, item name, category code and price.

2. Query for all items where the on hand greater than 50 OR on order greater than 50. When you show the results, be sure you include both on hand and on order as fields in the display.

3. Query for all items where the reorder point is greater than 100 AND then either the price is greater than 25 OR the cost is greater than 20

4. Query for all items where the years carried is greater than 3 AND it is either in one of your category codes or another of your category codes. (For example, in my clothes example you would be looking for either the W for womens or the M for mens).

5. Query for all items that are in one category with an on hand greater than 25 OR in another category with an on hand greater than 50.

Report and Form: You need to also make a form and a report with information from this table.  Use the wizard to make both the form and the report (see presentations for help).