Using lookup summary fields in forms or reports: example

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:

  1. Select Lookup Summary Field from the Insert menu and click once at the place where you want the field to appear.

  2. In "Insert Field Wizard" do the following:

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.

  1. Click on Next and then click on Finish.

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