Displaying Aggregate Expressions on a Main Form from a Subform

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:

Aggregate Count

 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: 

=Count([CustID]).

In the main form, the following expression will reference the subform:

=[SubformName].Form![NameOfTextBoxControl]

 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.

Aggregate Expressions as Shown in Design View

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.

19 Comments

  1. Keneth Dean

    Hi Susan, nice and very informative article you have here. Thanks for solving lots of my problems. Keep up the good work 🙂 and thanks

  2. San Diego Wheel Chair

    I was wondering what would be the best way to use an invoice system to show customers with related
    sales. After reading your post I learned that I should be doing it by using a main form and a subform. I’ll try that right now. Thanks! You helped me a lot!

  3. Nelson134

    By providing the information it is fantastic an it really saves a lot of time To calculate the order subtotal in the subform, you add a text box to the form footer of the subform and specify an expression in the ControlSource property box for the text box.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

  4. Jason

    Thanks a lot for this article. I m really glad to tell u that its a very nice way to learn learn access and Really u made my day off bcz of this article. thanks a lot.

  5. plaster molds

    As a system developer using Visual Basic, with the use of Microsoft access as it’s back end database, this has been of great help, i have been trying to figure out this kinds of solutions and you provided it.

  6. Steve Baldwin

    Thanks for posting such a nice article. People will be benefited for that. I am also helpfull for that. Many important information are include in here.

  7. Phoebe

    This is really a cool post. Although there are a lot of database software out there. I really like using MS Access for it very user friendly. Thanks a lot for the tutorial it is really helpful.

  8. defense

    Appreciating the commitment you put into your website and in depth information you present. It’s awesome to come across a blog every once in a while that isn’t the same old rehashed information. Fantastic read! I’ve saved your site and I’m adding your RSS feeds to my Google account.

  9. kata mutiara

    thank you for this information I love to read this post because it is very useful and easy to understand, after reading this page I will see see the other pages of this web hopefully have a lot of good information

Leave a Reply

Your email address will not be published. Required fields are marked *