Counting sub-groups

[liddlem]

Hi all
I have the 'Source data' (as per the sample data in the attached spreadsheet) but now I need to count groups of records to get the results to display as per 'Count Of'

This data is selected between Start_Dt and End_Dt which I have on a form called 'Frm_Reports'

I cant create crosstabs because the values are subject to change, which means that column headings are going to be all over the place.

And if I try creating counters in each section of the report, (see the range highlighted in blue) I cannot simply hide the respective headers/footers of each sub-group and then assign a total value in the footer of the 'Presenter' group. The total always comes to 339 - being the count of patients.

Can you point me in the right direction please.

 

[namliam]

In most "big brothers" of Access like SQL Server MySQL or Oracle you can do Count(Distinct Anycolumn) and get what you are after....

However unfortunately this option doesnt exist in Access as far as I know. Instead you have to do it in two steps something along the lines of:

Select Presenter, Count(*) Countingcolumn
from ( select presenter, ws_venue_id
         from yourtable
         group by presenter, ws_venue_id )
group by Presenter

Really something M$ should add to the Access SQL

 

[liddlem]

Thanks namliam
I'll give this a crack shortly.