Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Question Unanswered: how do i count unique values?

    Hi,
    Have spent several hours scouring the internet forums for a solution with no avail! I hope someone can help!

    I have a report for different companies, it lists their members information.

    I.E school 1 - member id, member's monthly payment

    At the end of the reports I have a count function counting the number of member id's for that school. (works fine)

    I am trying to also add the number of times that zero appears in the member's monthly payment list.

    I have triend things like;

    count[members payment] where [members payment] = 0

    and other things like this - the logic makes sense to me but my lack of experience in access means i can't write it correctly!

    I hope someone can help -
    Morgan

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Take a slightly different approach:

    =Sum(iif([members payment] = 0, 1, 0)

    Logic is slightly different then using count, but then again your desired result is slightly different then count. As you've noticed, Count() does not accept criteria, so we asked it to first translate the [members payment] field into either a 1 or a 0. If it meets our criteria it's counted as 1, 0 if it doesn't. Then we sum these "translated" values to effectivly return a count of the records matching the criteria.

    Make sense?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2004
    Posts
    3

    thanks

    worked great thanks!!!! getting there!

  4. #4
    Join Date
    Oct 2003
    Posts
    706
    Does MS-Access support SQL's COUNT(DISTINCT fieldname) syntax? And/or provide access to this through to the query-builder GUI? I don't recall.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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