Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: Unique count query

    I am trying to run a query so it returns a unique count of clients using our service. One client can use the service many times thus the client ID is repeated. I wish to find out how many clients use our service, how do i amend the following query so as not to count multiple instances of the same client:

    SELECT Count(tbl_CoreClientRefs.ID) AS CountOfID
    FROM tbl_CoreClientRefs INNER JOIN tbl_CoreClientContacts ON tbl_CoreClientRefs.ID = tbl_CoreClientContacts.CoreClientRefID
    WHERE (((tbl_CoreClientContacts.DateOfContact)>=[paramStartDate] And (tbl_CoreClientContacts.DateOfContact)<=[paramEndDate]));

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    If you switch Access to ANSI SQL mode then you can use the syntax COUNT(DISTINCT MyCol). Otherwise you need to get distinct IDs and then count e.g.:
    Code:
    SELECT Count(*) AS CountOfID
    FROM
    (SELECT DISTINCT tbl_CoreClientRefs.ID
    FROM tbl_CoreClientRefs INNER JOIN tbl_CoreClientContacts ON tbl_CoreClientRefs.ID = tbl_CoreClientContacts.CoreClientRefID
    WHERE tbl_CoreClientContacts.DateOfContact >=[paramStartDate] And tbl_CoreClientContacts.DateOfContact <=[paramEndDate]) AS DistinctIDs
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    thats great but how do you switch?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Tools-> Options-> Tables\ Queries

    Check the entry in Help first as you need to be sure your current stuff will work fine.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2006
    Posts
    65
    Thank you very much, it worked like a treat

Posting Permissions

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