This first worksheet limits the queries to only one database table.

Having completed Lab 12 and the lecture on Access Part 2, you should be able to perform the following tasks:

• Use multiple tables
• Define links
• Use a calculated field
• Use a summarized field

For this worksheet we will use the following Access tables Employee (Figure 1) and Salary (Figure 2):

Figure1: Employee Table

Figure 2: Salary Table

Your employer has determined a number of questions they want the database management system to answer. You must obtain answers to the questions posed by your employer.

Instructions: Use the Employee table above to fill in the design grid for each question.

1. Display for each employee, his or her employee identification, department name, last and first name and salary.

2. Display in alphabetical order the last names of employees who live in Indiana who make over $10,000. (IN)

3.) Sum the total salary of employees who live in Indiana. (IN)

4. Group employees (last name) by their department (list the department) and list the salary.

5. List the names (first and last) of employees and the amount they paid in local taxes ( salary * .001).