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:
Select Lookup Summary Field from the Insert menu.
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.
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.
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:
Click on the button to the right of the Join Source box. A list of all the tables (or queries or relations) in the current database is displayed. Click on the table you want to join to, and then click on OK.
In the Join Field box, click on the arrow to display a list of all the fields in the table you have just selected in Join Source. Click on one of these. (Note that it is your aim to match records in the table to which Data Field belongs with records in the Join Source table, so make sure you select fields that have the same data type and the possibility of some matching values. For example, you might select a Surname field in both.)
In the Lookup Field box, you select the field in the joined table that contains the information which is to be summarized. Since most summary information is calculated using numeric values, this should nearly always be a numeric field.
In the Lookup Formula box you select the type of formula to apply to Lookup Field. Choose from the following list:
Count (counts the number of matching records)
Sum (sums the values in the matching records)
Minimum (finds the minimum value in the matching records)
Maximum (finds the maximum value in the matching records)
Average (finds the average value of the matching records).
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.
Finally, click on Next and then on Finish to insert the lookup summary field at the place you chose.
See: