Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2010
    Location
    Idaho, USA
    Posts
    5

    Unhappy Unanswered: SUM the results of 2 counted fields

    I am trying to add the counted results of two fields. I have been away from MS Access for awhile and am new to SQL. I am thinking I need an SQL statement.

    So far, I created the following in design view. The SQL statement looks like:

    SELECT Patients.[Study Date], Patients.[CPAP Study Date], Count(Patients![Study Date])+Count(Patients![CPAP Study Date]) AS [Total Studies]
    FROM Patients
    GROUP BY Patients.[Study Date], Patients.[CPAP Study Date]
    HAVING (((Patients.[Study Date])>=#1/1/2010#)) OR (((Patients.[CPAP Study Date])>=#1/1/2010#));

    Results look like:
    Study Date, CPAP Study Date, Total Studies
    1/1/2010, 2/1/2010, 2
    2/12/2010, 2/12/2010, 2

    (i guess I can't create columns here, so I used a comma to separate)

    I want to SUM the Total studies field, which would be 4 in the above example.

    I have tried to SUM the Total Studies field from design view, but am running into various error messages. If I try to add an expression in a new field that Sums the Total Studies field I get the error msg "Subqueries cannot be used in the expression (Sum(+))". If I try to use SUM in the Total row I get an error like "Cannot have aggregate function in expression...")

    Any help would be immensely appreciated.

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Change the GROUP BY for Study Date and CPAP Study Date to WHERE this will give a final total without any grouping

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT 'details' AS rowtype
         , Patients.[Study Date]
         , Patients.[CPAP Study Date]
         , Count(Patients![Study Date]) + 
           Count(Patients![CPAP Study Date]) AS [Total Studies]
      FROM Patients
     WHERE Patients.[Study Date] >= #1/1/2010#
        OR Patients.[CPAP Study Date] >= #1/1/2010#
    GROUP 
        BY Patients.[Study Date]
         , Patients.[CPAP Study Date]
    UNION ALL
    SELECT 'totals'
         , NULL
         , NULL
         , Count(Patients![Study Date]) + 
           Count(Patients![CPAP Study Date]) AS [Total Studies]
      FROM Patients
     WHERE Patients.[Study Date] >= #1/1/2010#
        OR Patients.[CPAP Study Date] >= #1/1/2010#
    ORDER
        BY 1
         , Patients.[Study Date]
         , Patients.[CPAP Study Date]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2010
    Location
    Idaho, USA
    Posts
    5

    SUM the results of 2 counted fields

    Thank you. This code is beautiful. Works great! And your response was fast. I am wondering how I might change the code so the user can input the date range?

    Quote Originally Posted by r937 View Post
    Code:
    SELECT 'details' AS rowtype
         , Patients.[Study Date]
         , Patients.[CPAP Study Date]
         , Count(Patients![Study Date]) + 
           Count(Patients![CPAP Study Date]) AS [Total Studies]
      FROM Patients
     WHERE Patients.[Study Date] >= #1/1/2010#
        OR Patients.[CPAP Study Date] >= #1/1/2010#
    GROUP 
        BY Patients.[Study Date]
         , Patients.[CPAP Study Date]
    UNION ALL
    SELECT 'totals'
         , NULL
         , NULL
         , Count(Patients![Study Date]) + 
           Count(Patients![CPAP Study Date]) AS [Total Studies]
      FROM Patients
     WHERE Patients.[Study Date] >= #1/1/2010#
        OR Patients.[CPAP Study Date] >= #1/1/2010#
    ORDER
        BY 1
         , Patients.[Study Date]
         , Patients.[CPAP Study Date]

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sheilat33 View Post
    And your response was fast.
    and yet you took six weeks for this reply

    to let the user input the date range replace the dates with prompts
    Code:
     WHERE Patients.[Study Date] >= [ please enter date ]
        OR Patients.[CPAP Study Date] >= [ please enter date ]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2010
    Location
    Idaho, USA
    Posts
    5
    I know...very slow. Life threw me a curve ball.

  7. #7
    Join Date
    Jun 2010
    Location
    Idaho, USA
    Posts
    5
    ok...I get only one prompt for one date. How do I get a prompt for a start and an end date? Basically number of studies for a period of time.

    Quote Originally Posted by r937 View Post
    and yet you took six weeks for this reply

    to let the user input the date range replace the dates with prompts
    Code:
     WHERE Patients.[Study Date] >= [ please enter date ]
        OR Patients.[CPAP Study Date] >= [ please enter date ]

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sheilat33 View Post
    How do I get a prompt for a start and an end date?
    the same way, with a prompt, but with some different txt inside it

    so you could use [ enter start date ] and [ enter end date ]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2010
    Location
    Idaho, USA
    Posts
    5
    That is more like it. I am realizing, I have a lot to learn. Definietly will check out your book

    Quote Originally Posted by r937 View Post
    the same way, with a prompt, but with some different txt inside it

    so you could use [ enter start date ] and [ enter end date ]

Posting Permissions

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