Inserting a lookup summary field into a form or report

The lookup summary field establishes a join between a selected field in one table (the table that is attached to your form or report) and a matching field in a second table (the joined table). It then uses this join to summarize the information held in a selected numeric field from the joined table. The matching records from the joined table are grouped together according to the field values from the first table.

To insert a summary field into a form (or report) you need to be in form design mode (or report view) first, and then follow these steps:

  1. Select Lookup Summary Field from the Insert menu.

  2. The mouse pointer turns to a cross. Place this where you want the field to appear and click your mouse once. The "Insert Field Wizard" dialog is displayed, turned to the lookup summary field page.

  3. In the Data Field box you select a field from the attached table. Click on the arrow to display a list of all the fields in this table and click on one of these to select it.

  4. In the Lookup Field Source group box you select the table (or query or relation) and field you want to join to and the field in the joined table for which a summary is to be calculated:

Obviously this normally requires that the field you select in Lookup Field be of numeric data type. The exception is Count, which can be used to count the number of matching records irrespective of the data type of the Lookup Field.

  1. Finally, click on Next and then on Finish to insert the lookup summary field at the place you chose.

See:

Using lookup summary fields in forms or reports: example