In Microsoft Access, when a main form and subform are created based on one-to-many relationships, you can display aggregate information in the main form; for example, a total COUNT of customer records for each sales person or a SUM total of sales amounts that have been displayed in a subform.
As you scroll through this main form, many customers may be associated with each sales representative:
When placing a value in the main form, the aggregate function first must be placed in the subform and hidden. Then this aggregate function is referenced from within the main form.
In the example, since we are counting customer ID numbers, the Count aggregate function in the subform would look like this:
In the main form, the following expression will reference the subform:
where SubformName is the name of the subform that contains the aggregate function. The .Form means that the control references another form, and the ! is used as a separator. The NameOfTextBoxControl expression is the name of the control in the subform that contains the aggregate function. (Microsoft Access 2007 Bible, Wiley Publishing, Inc., 2007).
As you can see here, the subform is named fsubCustomers, and the control with the aggregate Count function is named txtcustcount. A label (# Customers) has also been added.
The customer count on the main form will update every time you change its value – in other words, every time you add or delete a customer, the number of customers for that sales person will change accordingly.