sistemalan
Registered User.
- Local time
- Today, 22:47
- Joined
- Jun 19, 2009
- Messages
- 77
Hi all,
I'd be really grateful if someone could help me figure this out. I'm trying to create a query to show historic attendance figures for a children's music club. The query should count how many unique children (ChildID) attended in each month.
So far what I've managed to do is get the total number of attendances, so if a child attended on 10 days they would be counted 10 times, whereas I want them to be counted just once so I can see how many unique children attended each month.
Here is the query in it's current form:
Trawling various forums has led me to realise that I need to involve the word DISTINCT at some juncture, and that I need a sub query.
Can one of you wonderful knowledgeable types help me figure out how to tweak the above query so that it only counts unique ChildID values for each month?
Many, many thanks!
Alan
I'd be really grateful if someone could help me figure this out. I'm trying to create a query to show historic attendance figures for a children's music club. The query should count how many unique children (ChildID) attended in each month.
So far what I've managed to do is get the total number of attendances, so if a child attended on 10 days they would be counted 10 times, whereas I want them to be counted just once so I can see how many unique children attended each month.
Here is the query in it's current form:
Code:
SELECT DatePart("yyyy",[AttendanceDate]) AS [Year], MonthName(DatePart("m",[AttendanceDate])) AS [Month], Count(T_Attendance.ChildID) AS [Total Signed Up]
FROM T_Attendance
WHERE (((T_Attendance.Attended)=True))
GROUP BY DatePart("yyyy",[AttendanceDate]), DatePart("m",[AttendanceDate]), MonthName(DatePart("m",[AttendanceDate]))
ORDER BY DatePart("yyyy",[AttendanceDate]), DatePart("m",[AttendanceDate]);
Trawling various forums has led me to realise that I need to involve the word DISTINCT at some juncture, and that I need a sub query.
Can one of you wonderful knowledgeable types help me figure out how to tweak the above query so that it only counts unique ChildID values for each month?
Many, many thanks!
Alan