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 explain why you choose the type and length that you choose. For example, if you are a analyst coming up with a database table for your customer list you need to analyze the data for potential customers. In determining how many characters you need in city, you should think of all of the local cities that your customers could come from and count the length. Then you should probably add a few characters just to be safe. In analyzing the price, you should determine the highest price for any product you currently sell. Then knowing that prices have a tendency to go up, you should consider this in determining the size of the field. For example, if the most expensive thing you currently sell is 75.00, you should anticipate that you really should have three digits for the whole number given prices tendency to spiral upward. 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.

The database table you are going to design is a simplified payroll table that should include the following fields.

Employee Number, Employee Name, Employee Address (should have separate areas for street address, city, state and zip), employee department (the departments in your company all have a 4 character code including letters and numbers), employee type (S for salaried, F for full time hourly, P for part time hourly, C for consultant), pay per hour (currently the highest pay per hour is 90.00), salary (currently the highest salary is 90,000.00), medical withholding (currently the largest amount is 4000.00), percent of pay withheld for state taxes, percent of pay withheld for federal taxes. The table also contains number of dependents and marital status (S, M, W, D, U).

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 employees who are salaried and make a salary greater than 50000 (show the employee number, employee name, employee type and salary).
2. Query for all employees who are either full time hourly or part time hourly (show the employee number, employee name and employee type).
3. Query for all employees who work in dept A123 and live in Massachusetts.
4. Query for all employees who are salaried and either married or have more than 3 dependents.
5. Query for all consultants who either work for dept A123 or dept B987.

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).