Skip to Main Content

go to Indiana University Indiana University


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 Responses to "Displaying Aggregate Expressions on a Main Form from a Subform" to “Displaying Aggregate Expressions on a Main Form from a Subform”

  1. jarno Says:

    This post resolve my problem, many thanks.

  2. paul Says:

    thank you susan, you save my day :)

  3. Keneth Dean Says:

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

  4. San Diego Wheel Chair Says:

    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!

  5. Taufik Says:

    Hi Susan,
    Thank you for writing this tutorial.. you save my time…

  6. bedava indir Says:

    good article

  7. hello world Says:

    Great tip. Helps design of tables too.

  8. diyet Says:

    Thanks for sharing your site, I follow the English, although a little worse

  9. Ben Says:

    Thanks a lot !!!! It was really helpful.

  10. Global Data Entry Says:

    This post was extremely useful, thanks for the info.

  11. Nelson134 Says:

    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

  12. miedo Says:

    All’s well that ends well.

  13. Jason Says:

    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.

  14. plaster molds Says:

    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.

  15. Steve Baldwin Says:

    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.

  16. Phoebe Says:

    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.

  17. defense Says:

    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.

  18. richardjensen4 Says:

    This is a really great post!You might want to visit this site.

  19. kata mutiara Says:

    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

 

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.