[Solved] Statistics of autofiltered records in subform

[gkick]

Hi,

I want to use a subform for drill down reporting, that is select the year,
then the month using the forms nav bar which works fine.
Below the form I want to display textboxes which may show the total of selected (filtered) records, average, highest ,....

Not sure if that actually can be done with using the nav bar for autofiltering as this does not provide a hook
for the where clause in the query for the textboxes

Do I need a filter table and listboxes for this or is there another option ?

Thanks for any ideas.

[Villeroy]

This is easily doable with a filter table and some SQL.

Code: Select all

SELECT SUM("blah") AS "Sum", COUNT("blah") AS "Count", AVERAGE("blah") AS "Average" 
FROM "Table", "Filter" 
WHERE "Filter"."ID"=1 
  AND (same criteria as the subform filtered by the same filter row)

[gkick]

Thanks Villeroy