Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56

    Unhappy Unanswered: Counting on a Query

    Hello,

    I have a query and I know this is easy but it's Monday morning here and my brain is yet to kick in.

    I have a query with:

    [Name of Trial]
    [Status]

    I need to add up all patients in [Status] grouped by [Name of Trial]

    …easy enough I can do that, but I want another field that adds up all patients in [Status] with a value of "follow up" grouped by [Name of Trial]

    Any ideas? I'm counting on you guys…(pardon the pun)

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Rusty's Way
    Hello,

    I have a query and I know this is easy but it's Monday morning here and my brain is yet to kick in.

    I have a query with:

    [Name of Trial]
    [Status]

    I need to add up all patients in [Status] grouped by [Name of Trial]

    …easy enough I can do that, but I want another field that adds up all patients in [Status] with a value of "follow up" grouped by [Name of Trial]

    Any ideas? I'm counting on you guys…(pardon the pun)
    Let's see how you're counting currently ... It should be easy to do this with a simple mod ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select [Name of Trial]
         , count(*)   as Statuses
         , sum(iif([Status]='follow up',1,0)) as Followups
      from yourtable
    group
        by [Name of Trial]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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