Counting Duplicates - I think I am missing something simple RRS feed

  • 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


    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,


    Query1 (based on table)

    Query2 (based on Query1)

    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
        (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