How to get a count of items in a Select Distinct query?

[..rick]

I have a table that records client attendance on a course and need to get a
count of course sessions given each month.

If I do a Select Distinct [dateattended] I get a cursor showing exactly what
is needed - for example:
2008-04-02
2008-04-09
2008-04-16
2008-05-01
2008-05-08
2008-05-15
2008-05-22
2008-05-29

There were 3 sessions held in April and 5 in May.

How do I coerce the count by month? When I do a Count on [dateattended] I
get the entire attendance and not the count of sessions.

Your advice is greatly appreciated.

Thanks

... rick

 

[KARL DEWEY]

Select Format([dateattended], "mmm yyyy" AS [Month_Year,
Count([dateattended]) AS [Number of Sessions]
FROM YourTable
GROUP BY Format([dateattended], "yyyymm";

 

[..rick]

Thank you for your reply Karl but I still get the count of records that
attended the Session on the DateAttended.
What I need is:
Apr May Jun Jul ...
04 05 04 0 ...
which is a count of the Distince Dates and not of the data records.

The sessions are held weekly, so there may be 0 to 5 in a month.
The Select DISTINCT [tblattendance.dateattended] gives me a cursor
containing the dates the course was held grouped by month.
I need to count the dates in the cursor and not in the tblattendance.

Thanks.

Select Format([dateattended], "mmm yyyy" AS [Month_Year,
Count([dateattended]) AS [Number of Sessions]
FROM YourTable
GROUP BY Format([dateattended], "yyyymm";
--
KARL DEWEY
Build a little - Test a little

I have a table that records client attendance on a course and need to get
a
count of course sessions given each month.

If I do a Select Distinct [dateattended] I get a cursor showing exactly
what
is needed - for example:
2008-04-02
2008-04-09
2008-04-16
2008-05-01
2008-05-08
2008-05-15
2008-05-22
2008-05-29

There were 3 sessions held in April and 5 in May.

How do I coerce the count by month? When I do a Count on [dateattended] I
get the entire attendance and not the count of sessions.

Your advice is greatly appreciated.

Thanks

... rick