Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: Creating a Sum of Counts in Access

    I am trying to create a sum of counts in access. I am making a query to show the number of reports (monthly, quarterly, semi-annual, annually) completed by a certain business day. The problem I am having is that I want the query to sum all of the the counts and not show the individual frequencies as it is currently doing. I have posted my SQL code below. Any help is greatly appreciated!


    SELECT Count(1) AS ["Number of Reports Finished"]
    FROM June
    WHERE (((June.[BD Sent])<[Business day completed by]))
    GROUP BY June.Frequency
    HAVING (((June.Frequency)=[Monthly] Or (June.Frequency)=[Quarterly] Or (June.Frequency)=[Semi-annual] Or (June.Frequency)=[Annual]));

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mcollins View Post
    I want the query to sum all of the the counts and not show the individual frequencies as it is currently doing
    then just remove the GROUP BY and change the HAVING clause (Access is so stupid) to WHERE
    Code:
    SELECT COUNT(*) AS ["Number of Reports Finished"]
      FROM June
     WHERE June.[BD Sent] < [Business day completed by]
       AND June.Frequency IN 
           ( [Monthly] , [Quarterly] , [Semi-annual] , [Annual] )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    6
    Thanks so much "r937"!

    If you could just provide one more peice of assistance with this small piece of code as well. Same thing here, just trying to sum these counts. I apologize if these are silly questions, I'm a little new at this.

    SELECT Count(1) AS ["Total Reports"]
    FROM June
    GROUP BY June.Frequency
    HAVING (((June.Frequency)=[Monthly] Or (June.Frequency)=[Quarterly] Or (June.Frequency)=[Semi-annual] Or (June.Frequency)=[Annual]));

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT Count(1) AS ["Total Reports"]
    FROM June
    WHERE (((June.Frequency)=[Monthly] Or (June.Frequency)=[Quarterly] Or (June.Frequency)=[Semi-annual] Or (June.Frequency)=[Annual]))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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