Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unanswered: how to do this tricky count()

    Hi Experts!

    here's what ive been stumped on for a couple of days now.

    Heres the 1st table

    ID Workshop College Dept Participant ID
    1 e-Training Business CIS 10
    2 Tax Forms Business CIS 10
    3 e-Training Law OE 5
    4 Tax Forms Law OE 5
    5 Tax Forms Law ASM 9
    6 e-Training Business CIS 19

    I want to run query #1 that can tell me this:

    Workshops College Dept Count_of_Participants
    e-Training Business CIS 2
    Tax Form Law OE 1

    I hope you're getting the point
    i want to count the number of UNIQUE participant IDs but heres the tricky problem, when i do a count like below it counts ALL the repeat IDs too which i dont want:

    SELECT DISTINCT count(participant_id), workshop, college, dept
    FROM table
    GROUP BY workshop, college, dept;

    i tried it without DISTINCT clause but as soon as there's one GROUP BY clause, the query disregards distinct and counts all repeat participant IDs.

    any help would be appreciated!
    thanks!
    chooza

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I think you're looking for incompatible results. You state that you want to count unique participant ids (there are 4), but I that's not the expected results you show.

    The first query results are obtainable:
    Workshops College Dept Count_of_Participants
    e-Training Business CIS 2
    Tax Form Law OE 1

    But if you want results that show
    ParticipId - ?
    10 - something
    5 - something
    9 - something
    19 - something

    That something is not coming from anything other than FIRST or MAX or similar. You can't group on both Participant ID and another of your fields to obtain the above.

    Maybe I don't understand what results you expect from your query?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    Re: how to do this tricky count()

    Check your Query design in the 'Design' view, as opposed to the SQL view. If Access has included your ID column, remove it from the query, even if 'show' is unchecked. It should work after that.

  4. #4
    Join Date
    Feb 2004
    Posts
    19
    Hi,

    let me rephrase what i said:

    i want to count the number of UNIQUE participants IDs meaning rows having participants IDs

    10
    10
    10

    should give me just 1 not 3 !
    i want the unique number of participants grouped by workshop, college, dept as below with the count of unique participant ids. why i cant seem to get this i tried to explain in post #1 above.

    e-Training Business CIS 2
    Tax Form Law OE 1


    thanks for any help you can provide!
    chooza

  5. #5
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    With the data you've provided, are these the results you've expected:

    Workshop College Dept CountOfParticipantID
    e-Training Business CIS 2
    e-Training Law OE 1
    Tax Forms Business CIS 1
    Tax Forms Law ASM 1
    Tax Forms Law OE 1
    All code ADO/ADOX unless otherwise specified.
    Mike.

  6. #6
    Join Date
    Feb 2004
    Posts
    19
    yep, thats exactly what i need

  7. #7
    Join Date
    Feb 2004
    Posts
    19
    drnw04a,

    im not including the ID column in the SQL statement. actually i prefer writing SQL than being in DESIGN view and i double check the SQL before and after execution to make sure ACCESS does not mess up the SQL statement when switching from DESIGN to SQL VIEW .

  8. #8
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    This is the query that generated the data:

    SELECT a.Workshop, a.College, a.Dept, Count(b.participantid) AS CountOfParticipantID
    FROM tblCount AS a INNER JOIN [select b.participantid, count(b.participantid) from tblCount b
    group by b.participantId]. AS c ON a.ParticipantID = c.participantid
    GROUP BY a.Workshop, a.College, a.Dept;
    All code ADO/ADOX unless otherwise specified.
    Mike.

  9. #9
    Join Date
    Feb 2004
    Posts
    19
    HoomerBoo

    that was terrific, worked great. youre a genius!


    here's my Query#2 which is related to the above one. I tried tweaking your query above but its not giving me the right results:

    Expanding the table thats above, heres the data sample

    Workshop College Dept P_ID
    e-Training Business CIS 2
    e-Training Law OE 1
    e-Training Business CIS 5
    Tax Forms Business CIS 5
    Tax Forms Law OE 3
    Tax Forms Law OE 4
    Tax Forms Business CIS 2


    Notice that some P_ID are repeating but i just want a query "that tells me X number of people from this dept in this college have an entry"

    The results i want are:

    College Dept CountOfPID

    Business CIS 2
    Law OE 3

    that is, count of DISTINCT P_ID grouped by college, dept.

    thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •