This example shows you how to use lookup summary fields in forms and reports (see Inserting a lookup summary field into a form or report).
You have two tables in a database, one called Employees which contains employee names and ID numbers, and another called Holidays, which contains employee ID numbers and information on holidays yet to be taken by each employee by the end of the year, excluding the general Christmas holiday.
Employees
ID |
Surname |
First_name |
1 |
SMITH |
JACKIE |
2 |
ROACH |
PHILIPPA |
3 |
ENGLAND |
CHRIS |
4 |
COLEMAN |
JOEL |
5 |
SMITH |
JAMES |
6 |
HOWLETT |
PAULINE |
Holidays
ID |
Holidays |
Days |
6 |
November 2 - 5 |
4 |
5 |
October 2 - 15 |
10 |
4 |
October 3 - 7 |
5 |
6 |
December 1 - 5 |
5 |
1 |
October 21 - 23 |
3 |
4 |
December 2 - 4 |
3 |
5 |
November 18 - 22 |
5 |
Your aim is to make a summary of each employee’s holiday entitlement. Note that not all the employees have holiday entitlement and that of those who do some have more than one period, that is to say, they are listed more than once in the Holidays table. Note also that there are two employees called Smith, hence the importance of using a unique identifier, in this case an ID number, for each employee.
You can insert a lookup summary field into any form or report that is already attached to the Employees table. Make sure you are in form design mode or report view first.
To insert a lookup summary field follow these steps:
Select Lookup Summary Field from the Insert menu and click once at the place where you want the field to appear.
In "Insert Field Wizard" do the following:
In the Data Field box, select the ID field from the Employees table.
In the Join Source box, select the Holidays table.
In the Join Field box, select the ID field from the Holidays table as the common field.
In the Lookup Field box, select the field from which you want to extract summary information. Obviously in this case it is the Days field in the Holidays table.
In the Lookup Formula box, select one of Count, Sum, Minimum, Maximum and Average. In this case select Sum. These work as follows:
Count gives the number of periods of holiday for each employee;
Sum gives the total number of days of holiday for each employee;
Minimum and Maximum give the number of days of holiday in the shortest and longest periods respectively for each employee;
Average gives the average number of days of holiday for each employee across all the periods.
Click on Next and then click on Finish.
It is a good idea to save the form (or report) at this point. Select Save Form (or Save Report) from the File menu and give the form a name.
The summary field is inserted.
If you have inserted the lookup summary field into a form, click on the Browse button to go to browse mode. You can now use the Record buttons to move through the forms. Each form corresponds to a record in the original Employees table.
Since you have selected Sum in the Lookup Formula box, the result in the summary field in each form will be the total number of days of holiday for the corresponding employee i.e. the corresponding ID number.
For employees without holiday entitlement, that is those with ID numbers that do not appear in the Holidays table, a #REF message is displayed in the summary field, indicating that there is no reference from that ID number in Employees to a corresponding ID number in Holidays.