Counting Duplicates - I think I am missing something simple

• Question

• I have a table of members who attended meetings.  It has the member ID and the month they attended. What I need to know is those that how many people just attended one meeting (that part is done) and then how many attended 2 - 12 meetings. Is there a simple way to group this? I did a duplicate query so I do know who attended more than one. I am just not sure how to count it for a result similar to

2 meetings .... count

3 meetings .... count

4 meetings ..., count

etc.

I would pull it out and try something in excel but I have close to a million records I am working with.

Any help would be appreciated.

Wednesday, July 11, 2018 4:48 PM

All replies

• Hi,

Have you tried using another query against your count query to count the counts? In other words,

Table
ID1
ID2
ID1
ID2
ID2
ID3
ID3

Query1 (based on table)
ID1=2
ID2=3
ID3=2

Query2 (based on Query1)
2Mtgs=2
3Mtgs=1

Hope it makes sense...

Wednesday, July 11, 2018 5:05 PM
• I am not sure what you mean.

I am thinking some kind of self join - Just not sure.  I was hoping I could do a count where the month is April and May and June - of course it can't be that simple.

Wednesday, July 11, 2018 5:21 PM
• I think I might have found a quick solution.  When I run the duplicates query and only pull in my member numbers - it sums up the number of records for the member number. I hope this is enough for my reporting.
Wednesday, July 11, 2018 5:28 PM
• Hi,

What I was saying earlier was to create a new query based on your duplicates query, so you can group by the Count column from the duplicates query and then Count() them. For example:

SELECT CountOfID, Count(CountOfID) As Total

FROM DuplicatesQuery

Hope it makes sense...

Wednesday, July 11, 2018 6:45 PM
• You don't need to check for duplicates.  You can return the number of attendances per member in a subquery, and then group by the number of attendances and return the count of rows per group in the outer query, e.g.

SELECT Attendances, COUNT(*) AS NumberOfMembers
FROM
(SELECT MemberID, COUNT(*) AS Attendances
FROM MeetingsAttended
GROUP BY MemberID) AS MemberAttendances
GROUP BY  Attendances;

where MeetingsAttended is the table name and MemberID is a foreign key column in the table.  This will return the number of members per meetings attended from 1 to 12.

Ken Sheridan, Stafford, England

Wednesday, July 11, 2018 10:38 PM