Advanced Queries and Reports - Exercises



UCL

Education & information support division

information systems

Access 2003

Advanced Access

Queries and Reports

Exercises

Contents

Exercise 1 - Inner and Outer Joins 1

Exercise 2 - Crosstab Queries 1

Exercise 3 - Make-Table Queries 1

Additional Exercise - Make-Table Queries 1

Exercise 4 - Append Queries 2

Exercise 5 - Delete Queries 2

Exercise 6 - Update Queries 3

Exercise 7 - Grouping and Aggregate Functions 3

Exercise 8 - Calculated Fields in Queries 3

Exercise 9 - Top Values 3

Exercise 10 - Calculated Fields on Reports 4

Exercise 11 - Parameters on a Report 5

Exercise 12 - Numbering Items on a Report 5

Exercise 13 - Subreports 5

Exercise 14 - Conditional Formatting 6

Unless stated otherwise, the following exercises should be done using the Access database exercises.mdb.

Exercise 1 - Inner and Outer Joins

1. Add a new Department to the tbl_Departments table. Give it Dept_ID 800 and Dept_Name Estates and Facilities. All other details should be the same as the Customer Services Department.

2. Create a Query based on the tbl_Employees and tbl_Departments Tables. Display the following Fields: Dept_ID, Dept_Name, Staff_ID, First_Name and Last_Name.

3. Run the Query. How many records are displayed?

4. Change the Join Properties to a Left Outer Join (the second Join option) and run the Query. Which additional record or records are displayed? Why?

5. Save the Query as qry_24-1.

Exercise 2 - Crosstab Queries

In this exercise you will create a Crosstab Query to find the number of employees in each department who hav a specific Ethnicity or Nationality.

1. Create a Crosstab query using the Crosstab Query Wizard, based on tbl_Employees.

2. Add Ethnicity and Nationality as row headings.

3. Add Dept_ID as column headings.

4. Use the Count Function on the Staff_ID Field.

5. Save the Query as qry_27-1.

Exercise 3 - Make-Table Queries

The Sales Manager has requested a Table containing information about all the current Employees in his Department.

1. Create a Query based on tbl_Employees.

2. Add all the Fields.

3. Add appropriate criteria to include only Employees in the Sales Department (Dept_ID is 300) who are current employees (the Field named Date_Left contains the last day of employment for each staff member and if this Field is null (empty) the person is a current employee).

4. Run the Query and view the results.

5. Amend the Query so that the Dept_ID Field and the Date_Left Field are not displayed when the Query is run.

6. Change the Query to a Make-Table Query to make a Table called tbl_Employees_Sales.

7. Run the Query and save it as qry_28-1.

8. Find and open the new Table you have created.

Additional Exercise - Make-Table Queries

1. Open exercise12.mdb.

2. Open tbl_Employees and view the Department information for the Employees.

Why is this not good database design?

3. Create a Query in design view which includes all the Fields with Department information from tbl_Employees.

4. Change the Query to a Make-Table Query to make a Table called tbl_Departments.

5. Add Grouping so that each Department is displayed only once.

6. View the results in Datasheet view and when you are satisfied with the results, run the Query.

7. Save the Query as qry_28-2.

8. Open the new Table to view the data.

Exercise 4 - Append Queries

You are going to add some new departments to tbl_Departments using an Append Query. The records you wish to append are in tbl_Departments_append.

1. Open tbl_Departments_append and tbl_Departments and view the data in each. Note particularly the data in the Dept_ID Field.

2. Create Query and add the appropriate Table and add all its Fields.

3. Make this Query into an Append Query and select the appropriate Destination Table.

4. Add all the available Fields to the Query Grid. One of the Fields doesn’t have a Destination Field in the Append to Row. Which one and why? Select an appropriate Field for this Field in the Append to Row.

5. Save the Query as qry_30-1 and close it.

6. Run the Query by double-clicking on it in the Database Window. What error message appears and why?

7. Click Yes to ignore the error and run the Query.

8. View the results by opening tbl_Departments to view the data.

Hint: You may also wish to view the data in tbl_Departments_append to see which records were appended and which were not.

Exercise 5 - Delete Queries

The company has decide to delete records of past employees from the database who left the company more than two years ago.

1. Open tbl_Employees and view the data. Note in particular the data in the Field named DateLeave which contains the last day of employment for each staff member. Null (empty) Fields indicate that the person is a current member of staff. How many staff are there altogether? How many have left?

2. Close tbl_Employees.

3. Make a copy of tbl_Employees and name it tbl_Employees_New.

4. Create a Query and add the table tbl_Employees_New.

5. Change the Query into a Delete Query.

6. Add the appropriate Field and enter the appropriate Criteria to delete records for all Employees who left before January 2003.

7. Switch to Datasheet view to see which records will be deleted. Don’t run the Query yet!

8. Run the Query.

9. Save the Query as qry_31-1 and close it.

10. Re-open tbl_Employees_New to view the results. You may wish to open tbl_Employees as well to compare the results.

Exercise 6 - Update Queries

Human Resources have decided that the longest notice period for any Department should be two months.

1. Create an Update Query to change all Departments with a notice period of 3 months to a notice period of 2 months.

2. Run the Query and view the results in the Table tbl_Departments.

3. Save the Query as qry_29-1.

Exercise 7 - Grouping and Aggregate Functions

1. Create a Query based on the tbl_Employees and tbl_Departments Tables. Display the following Fields: Dept_ID, Dept_Name and Staff_ID.

2. Select appropriate Grouping and Aggregate Functions to view the number of Employees in each Department.

3. Change the Join Properties to Left Outer Join and view the results. What has changed?

4. Save the Query as qry_25-1.

Exercise 8 - Calculated Fields in Queries

A one-off bonus of 10 % of their Salary is to be paid to all current employees. When employees leave the company the date they leave is displayed in the Date_Left Field.

1. Create a Query to display only current staff and their Salary information:

• Include only the Staff_ID, First_Name, Last_Name, Date_Left and Dept_ID Fields from tbl_Employees and the Salary Field from tbl_Salaries.

• Add appropriate Criteria to exclude staff who have left the company

2. Add a calculated Field to calculate 10% (0.1) of Salary and name the Field ‘Bonus’.

3. Run the Query to view the results.

4. Format the Bonus Field as currency with no decimal places.

5. View the results. The Query should display all Salary records for current employees with a Bonus calculation for each.

6. Save the Query as qry_26-1.

Additional Task (Grouping and Aggregate Functions)

7. Add Grouping and Aggregate Functions to this Query to show only one record for each Employee with their current Salary and the corresponding Bonus calculation.

Hint: the current Salary will be the highest Salary amount ever paid to any given Employee. Use the same aggregate function for the Salary and Bonus Fields

Exercise 9 - Top Values

This exercise also involves using Grouping and Aggregate Functions.

You want to see the 5 longest serving employees in your company. Their first SalaryDate indicates when they commenced their employment with the company.

1. Create a Query and add the Tables tbl_Employees and tbl_Salaries.

2. Add appropriate Fields to view Employee information and SalaryDate

3. Add Grouping and an appropriate Aggregate Function to view only the earliest SalaryDate for each Employee.

4. Choose the appropriate Sort option and Top Values option to view only the 5 Employees who have worked at the company the longest.

5. View the results of your Query. How would you change this Query to view the 5 newest Employees?

6. Save the Query as qry_32-1

Exercise 10 - Calculated Fields on Reports

This exercise requires you to use qry_26-1 (created in Exercise 8). If you haven’t completed Exercise 8, use qry_26-1_answer for this exercise.

1. Create a Report using the Report Wizard:

• Add all the Fields from the Query qry_26-1 (created in Exercise 8) except Dept_ID and Date_Left and all the Fields from tbl_Departments except Dept_ID and Notice_Period.

• Choose to view your data by tbl_Departments

• Sort your records by Last_Name and then by First_Name.

• Choose the Stepped Layout option and Landscape Orientation

• Name the Report rpt_Employees_bonus

2. Change all the margins to 15 mm.

3. Increase the report width to 26.5 cm.

4. Add a Calculated Field to calculate the new salary for each employee:

• Add a Text Box and Label to the right of the Bonus Field.

• Type ‘New Salary’ in the Label

• Create a formula in the Text box to add the Bonus to the Current Salary

Hint: Be careful to use the correct Control Names in your formula.

5. Change the Grouping and Sorting properties so that the Group Footer for the Department Group is displayed.

6. Add a Calculated Field to calculate the total bonuses due to employees in each department:

• Add a Text Box and Label to the Group Footer so that the Text Box is immediately below the Bonus Field.

• Type ‘Departmental Total’ in the Label

• Create a formula in the Text Box to find the sum of the Bonuses for each Department.

7. Format the Calculated Fields created (above) as currency.

Additional Tasks

8. Create a Calculated Field to find the total Bonuses for all Departments.

(Where would you put this Field?)

9. Create a Calculated Field to find the average Bonus for each Department.

Exercise 11 - Parameters on a Report

1. Open rpt_Employees_bonus (created in Exercise 10) in Design View. (If you have not completed Exercise 10, follow Step 1 of Exercise 10 to create a Report using the Wizard.)

2. Add a Parameter Field to display the name of the person who last edited the Report.

• Add a Text Box and Label to the Report Header

• Type ‘Last edited by’ in the Label

• Add a Parameter to the Text box with the text ‘Enter your name below’

3. Run the Report to test your Parameter.

Exercise 12 - Numbering Items on a Report

1. Open rpt_Employees_bonus (created in Exercise 10) in Design View. (If you have not completed Exercise 10, follow Step 1 of Exercise 10 to create a Report using the Wizard.)

2. Add a Running Sum Control to number the employee records over the whole Report.

3. Run the Report to view the results.

4. Return to Design View and set the Running Sum to restart numbering for each Department.

5. Run the Report to view the results.

Exercise 13 - Subreports

1. Create a Report using the Wizard:

• Base the Report on tbl_Departments

• Add all the Fields

• Sort by Dept_Name

• Set the Orientation to Landscape

• Name the Report rpt_Departments

2. Add a Subreport to the Detail Section (below the existing Field Controls):

• Increase the height of the Detail section

• Use the Toolbox to add a Subreport based on tbl_Employees

• Add First_Name, Last_Name, Job_Title, Telephone and Dept_ID Fields

• Name the Subreport rpt_Employees_subreport

3. Remove the Subreport Label.

4. Reduce the height of the Subreport to a minimum

Additional Tasks

5. Add a border around the Subreport Control

6. Open the Subreport from the Database Window and add a Label to the top of the Report Header with an appropriate heading for the Report.

7. Remove the Dept_ID Field from the Subreport.

8. Close the Subreport and open rpt_Departments to view the results.

Exercise 14 - Conditional Formatting

1. Open the Report created in Exercise 10 in Design View.

(If you have not completed Exercise 10, follow Step 1 of Exercise 10 to create a Report using the Wizard.)

2. Apply Conditional Formatting to the Bonus Field so that the Font colour changes to Red if the Bonus value is greater than £1900.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download