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

    Unanswered: Distinct count in following query

    Hi,
    i need to query a database doing distinct counts. As i am a beginner in Access i am struggling to get a unique count of field 'client_id' using the following query (it works but does not give unique count of client_id):

    SELECT T_contact.purpose, T_contact.outcome, Count(T_client.client_id) AS CountOfclient_id
    FROM (T_client INNER JOIN T_episode ON T_client.client_id = T_episode.client_id) INNER JOIN T_contact ON T_client.client_id = T_contact.client_id
    WHERE (((T_episode.initial_assessment_date)>=[paramStartDate] And (T_episode.initial_assessment_date)<=[paramEndDate]))
    GROUP BY T_contact.purpose, T_contact.outcome
    HAVING (((T_contact.purpose)="Assessment") AND ((T_contact.outcome)="DNA"));

    if anyone can solve this problem i will be most grateful

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT purpose
         , outcome
         , count(*) as distinct_clients
      FROM (
    SELECT DISTINCT
           T_contact.purpose
         , T_contact.outcome
         , T_client.client_id
      FROM (
           T_episode 
    INNER 
      JOIN T_client 
        ON T_client.client_id = T_episode.client_id
           ) 
    INNER 
      JOIN T_contact 
        ON (
           T_contact.client_id = T_client.client_id
       AND T_contact.purpose = 'Assessment'
       AND T_contact.outcome = 'DNA'
           )
     WHERE T_episode.initial_assessment_date >=[paramStartDate] 
       AND T_episode.initial_assessment_date <=[paramEndDate]
           ) as data
    GROUP 
        BY purpose
         , outcome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    fantastic! works well,
    many 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
  •