count number of same dates

[Van]

Great to see the Lounge again!

I am trying to work out how to count the number of instances of the same date returned in a crosstab query for a report. We run a clinic once (sometimes twice) per week, in which we see around four to six patients. I need to count the number of clinics run per month (which I will then show on a report along with the average number of patients seen per clinic).

Can anyone point me in the right direction as to how this is done?

Many thanks,
Van

[Carol W.]

I see that our guru, Hans, is away for a while so I'll attempt to provide an example of an application I wrote several years ago that contains cross tab reports. See attached. I wrote the cross-tab reports with a lot of Hans' help.

Click on Reports Submenu. Try running any of the reports (with the exception of the one entitiled "General Query ..."). If you use start and end dates of 1/1/2010 and 12/31/2010, you should see data in the reports.

There's a lot of VBA code behind the reports.

Is this what you're looking for?

[Van]

Hi Carol,

Thanks for the reply. It's not quite what i'm after though. I understand how to use parameters to return dates in the chosen range. What I want to do is then count the total number of dates returned....but count unique instances only. For example, if the query returns the dates:

1/1/10
1/1/10
2/1/10
3/1/10

the count of unique dates would be 3.

Regards,
Van

[mbarron]

The SQL would be:
SELECT Count(Dates) AS DateCount FROM (select distinct dateField as dates from tbldates) as GrpDates

or

SELECT Count(Dates) AS dateCount FROM (select dateField as dates from tblDates group by dateField) as GrpDates

The second SQL would be created by using queries in the following manner:
A query with a single field(the date field) grouped on that filed (GrpDates in the attached)
Use that query as the source of a second query grouped on the date field with an operator of Count (qryFinal in the attached)

The attached shows the three methods

[Carol W.]

I think I see what you want. See attached sample database.

The query called qrycount returns 3, using your example dates in tbltest. qrycount is based on qrytest which, in turn, is based on tbltest.

Hope this is clear.

[Van]

thanks mbarron, that's just what i'm after. i'll give it a go.

regards,
van

[Van]

thanks Carol. that works well.

regards
Van

[HansV]

Hi Van,

Welcome to Eileen's Lounge!