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

    Unanswered: which clients attended 3 sessions?

    Hi,
    i need to query a client database to list the clients that have attended a certain number of sessions. This includes listing clients that have attended 3, 6 or 9 sessions. Each client has a clientid and if a session is attended then there will be a contactid created in the contact table. A client can have many contacts attributed to it. The outcome in the contacts table is set to "attend" if the client turns up for a session.

    any help will be most appreciated!

  2. #2
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Hi, I do something similar, with part movements; to attain the result I have to use two queries, the first gathers the data.

    1st Qry
    Pull through (ClientID) & (ContactID)- Count this field

    2nd Qry
    From the above Qry pull through ClientID CountOf(ContactID)

    For the purpose of the example I will rename CountOf(ContactID) to CCID

    Depends what you want to do here, I used this If statement as an example

    NoOfSessions: IIf([CCID]<3,[ CCID)],IIf([CCID]>=3 And [CCID]<5,"3 to 5",IIf([CCID]>=6 And [CCID]<8,"6 to 8",IIf([CCID]>=9,"9+"))))
    Attached Files Attached Files
    Last edited by MarkWhyte; 01-16-08 at 07:00.

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    hi
    i am a beginner with access/sql, do i use the if statement as a macro or within the query?

  4. #4
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Hi look at the attached example

    The query to look at which will give you an idea "Part Analysis 2"

    Mark
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT c.clientID
         , Count(*)
    FROM   clients c
     LEFT
      JOIN sessions s
        ON s.clientID = c.clientID
    WHERE  outcome = 'Attend'
    GROUP
        BY c.clientID
    Add the following optional clause to find those whose count is of a specified value (e.g. 3)
    Code:
    HAVING Count(*) = 3
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2006
    Posts
    65
    thank you both,

    will give it a try!

Posting Permissions

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