Counting Distinct Values in a Totals Query?

[sistemalan]

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:

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

 

[MarkK]

You need to group by the date period in question, AND by the child. Then count the attendance rows.

 

[sistemalan]

Hi,

I am grouping by date period. Grouping by ChildID does not produce the desired result. It simply shows the number of times each child attended each month. What I want is the number of unique children who attended each month.

Much obliged for any help in modifying the above query.

Alan

 

[sistemalan]

I'm go to try and explain this another way. I could really use some help with this and I think it must be really straight forwards, but I've been trying for days now and my most recent efforts have been making Access hang!

I've attached a screenshot of the table T_Attendance to show the structure. It simply records:

AttendanceID - Autonumber
AttendanceDate - The date
ChildID - The Unique ID number for a child
Attended - Boolean value to say whether they came or not

For every day in the project's history there will be a record for each child (who was expected on that day) To say whether they attended or not. There are currently 38,000 records in the table.

I need to create a query which will tell me how many distinct children attended each month, I.e.

2013 September 76
2013 October 74
2013 November 82
2013 December 67
2014 January 57
2014 February 84

etc...

The query at the start of this thread show the total number of Attendances (i.e. It counts each child the number of times they came rather than just once). I've been trying to solve this using Dcount but Access has been crashing every time I run the query.

Please Help!

 

[plog]

I need to create a query which will tell me how many distinct children attended each month

You will need a subquery. This is the SQL for that subquery:

SELECT Year(AttendanceDate) AS YearAttended, Month(AttendanceDate) AS MonthAttended, ChildID
FROM T_Attendance
WHERE Attended=TRUE
GROUP BY Year(AttendanceDate) AS YearAttended, Month(AttendanceDate) AS MonthAttended, ChildID

Then you build another query using that subquery as its datasource. Bring in all the fields, GROUP BY YearAttended and MonthAttended, Count ChildID.

 

[sistemalan]

Thank you so very much indeed. I knew it would be simple, I just couldn't figure it out.

Cheers!