none
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

    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