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:
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.
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.
March 26th, 2010 at 8:57 pm
This post resolve my problem, many thanks.
June 23rd, 2010 at 8:11 am
thank you susan, you save my day
June 27th, 2010 at 12:44 am
Hi Susan, nice and very informative article you have here. Thanks for solving lots of my problems. Keep up the good work
and thanks
July 25th, 2010 at 2:44 am
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!
July 26th, 2010 at 8:27 am
Hi Susan,
Thank you for writing this tutorial.. you save my time…
August 2nd, 2010 at 4:04 pm
good article
August 10th, 2010 at 10:06 pm
Great tip. Helps design of tables too.
August 16th, 2010 at 6:35 am
Thanks for sharing your site, I follow the English, although a little worse
September 2nd, 2010 at 12:56 pm
Thanks a lot !!!! It was really helpful.
September 3rd, 2010 at 4:44 pm
This post was extremely useful, thanks for the info.
August 28th, 2011 at 4:44 am
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
November 12th, 2011 at 9:51 pm
All’s well that ends well.
November 14th, 2011 at 5:39 am
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.
December 2nd, 2011 at 8:43 pm
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.
May 12th, 2012 at 9:21 pm
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.
October 20th, 2012 at 10:42 pm
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.
November 10th, 2012 at 10:50 am
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.
November 28th, 2012 at 9:46 pm
This is a really great post!You might want to visit this site.
February 25th, 2013 at 11:08 am
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