<< PREVIOUS   Exercise 2 - Create Queries

Your boss has asked you to search the Employee table in the Company Database that you created earlier. You will need to set up queries to obtain the information required.

1. Open the Staff Database

Setup Query

1. Click Queries on Left panel in the Objects Window. Double click Create Query in Design View.

2. Click the Employee Table (it should be automatically highlighted because it is the first on the list.

3. Click Add, Click Close (the Employees field list will appear at the top of the Query design window).

Query 1. Who are the Female Staff? (text based)

Create Query

1. Click the Field row in the first column, click the down arrow on the right and click Forename from the field list.

2.Repeat the process above and select Surname, Job Title and Gender as field names in the next three columns.

3. In the Gender column, type the following in the Criteria row: “Female”

4. Click Run on the toolbar to view the results.

Save & Print the query

5. Save the query as: Females leave a space and type your name.

6. Click File menu / Print / O.K.

7. Click File menu / Close.

Note! To modify your Query, if you have closed your query, click Query on the left in the Objects Window. Double click the query you want to open it. Click Design view and make the necessary changes. Click Run on the toolbar to view the results. Click File menu / Save.

Query 2. Who has joined the Pension Scheme? (text based)

1. Follow instructions for Setup Query.

Create Query

1. Click the Field row in the first column, click the down arrow on the right and click Forename from the field list.

2. Repeat the process above and select Surname, Job Title and Pension Scheme as field names in the next three columns.

3. In the Pension Scheme column, type the following in the Criteria row: “Yes”

4. Click Run on the toolbar to view the results.

Save & Print the query

5. Save the query as: Pension leave a space and type your name.

6. Click File menu / Print / O.K.

7. Click File menu / Close.

Query 3. Who has earns more than £20,000 a year? (number based)

1. Follow instructions for Setup Query.

Create Query

1. Click the Field row in the first column, click the down arrow on the right and click Forename from the field list.

2. Repeat the process above and select Surname, Job Title and Salary as field names in the next three columns.

3. In the Salary column, type the following in the Criteria row: >20000

4. Click Run on the toolbar to view the results.

Save & Print the query

5. Save the query as: Salary > £20,000 leave a space and type your name.

6. Click File menu / Print / O.K.

7. Click File menu / Close.

Query 4. Who has earns more than £20,000 a year? (text based)

1. Follow instructions for Setup Query.

Create Query

1. Click the Field row in the first column, click the down arrow on the right and click Forename from the field list.

2. Repeat the process above and select Surname, Town, Postcode and Job Title as field names in the next four columns.

3. In the Postcode column, type the following in the Criteria row: “W*”

4. Click Run on the toolbar to view the results.

Save & Print the query

5. Save the query as: West London leave a space and type your name.

6. Click File menu / Print / O.K.

7. Click File menu / Close.

Query 5. Who is not in a pension scheme and was born after 1970 with an income of under £30,000 (text, date and number based)

1. Follow instructions for Setup Query.

Create Query

1. Click the Field row in the first column, click the down arrow on the right and click Forename from the field list.

2. Repeat the process above and select Surname, Date of Birth, Salary and Pension Scheme field names in the next four columns.

3. In the Date of Birth column, type >#31/12/1970# in the Criteria row.

4. In the Salary column, type <30000 in the Criteria row.

5. In the Pension Scheme column, type “No” in the Criteria row.

6. In the Salary column, click the Sort row, click the arrow on the left, click Ascending.

7. Click Run on the toolbar to view the results.

Save & Print the query

8. Save the query as: No pension >1970 <£30000 leave a space and type your name.

9. Click File menu / Print / O.K.

10. Click File menu / Close.