Aggregating subform data

[MarioUY]

Hi,

I’ve a Form with several Subforms linked by Master/Child and a series of filters in the main form to select certain groups of records. My preferred way to filter is by using Recordsource with different Where clauses.

I want to display all kinds of stats (like Count, Sum, etc) for the ENTIRE datasets that remain under each subform.

To give a concrete example: say that the Main Form displays a list of Authors from many countries and say there is a subform with the list of all their books. Suppose form and subform are linked by a BookID key.

Now, filter the main form to show all the British authors and want to display the TOTAL number of books that there under the filtered subform, in other words how many books were written by those authors.

Surely this can be achieved by using many separate queries (with subqueries when needed) and by displaying the looked-up values, but I wonder if there is a better, more elegant way to accomplish it.

Appreciate your help!

 

[plog]

Now, filter the main form to show all the British authors and want to display the TOTAL number of books that there under the filtered subform, in other words how many books were written by those authors.

I would put an unbound control on the main form for that total. On initial opening it would use a DCOUNT to determine the total and show it. Upon the filter you use another DCOUNT with your filter criteria to reload it appropriately.

 

[Ranman256]

in the subform footer, the text field datasource would be like:
=Sum([valueFld])
or
=Count([field2])

 

[MarioUY]

Thanks plog. The total for the main form is what I actually do however once filtered I cannot use DCOUNT because in my example the DOMAIN is in Books (I want to count the number of books) but the criteria uses info in the main table (AUTHORS - british) thus the need to use a query.

Ranman256 thanks but that won't solve the problem because the main form is a single form of an AUTHOR in my example and the subform displays his books not all the british books - Count() will show only books for the specific Author.

 

[plog]

So build that query (Authors & Tables) bring in all fields from both tables and then do your DCount on that query where you will be able to apply criteria.

 

[MarioUY]

Right, that is the normal way but the actual implementation needs many queries and I was wondering whether somebody has a better idea to build a solution.

Thanks!

 

[MajP]

If you want to filter the main form in many different ways and you want the count of the mainform. Why not count the recordset of the main form?
Me.Recordsetclone.recordcount

Public sub Form_Current
me.txtCount = me.recordsetclone.recordcount
end sub

This should update on any applied filter since that triggers the on current.

 

[MarioUY]

If you want to filter the main form in many different ways and you want the count of the mainform. Why not count the recordset of the main form?
Me.Recordsetclone.recordcount

Public sub Form_Current
me.txtCount = me.recordsetclone.recordcount
end sub

This should update on any applied filter since that triggers the on current.

Because, as I understand it won't give the answer I want, the count of ALL remaining records in the linked table (the subform table BOOKS)