Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Red face Unanswered: Count queries require additional SUM queries ?

    I have three queries that do not sum! (See Below).

    I had to create three additional SUM queries to SUM the total of each of these because I can't figure out how to get them to SUM.

    What the heck am I missing?

    SELECT Count(tblCARETSData.City) AS CountOfCity
    FROM tblCARETSData
    WHERE (((tblCARETSData.City)="bd" Or (tblCARETSData.City)="cthc" Or (tblCARETSData.City)="coa" Or (tblCARETSData.City)="dctr" Or (tblCARETSData.City)="dhs" Or (tblCARETSData.City)="ds" Or (tblCARETSData.City)="iwld" Or (tblCARETSData.City)="iw" Or (tblCARETSData.City)="ind" Or (tblCARETSData.City)="lq" Or (tblCARETSData.City)="mec" Or (tblCARETSData.City)="mntc" Or (tblCARETSData.City)="pdst" Or (tblCARETSData.City)="pspr" Or (tblCARETSData.City)="rm" Or (tblCARETSData.City)="salt" Or (tblCARETSData.City)="sltb" Or (tblCARETSData.City)="th" Or (tblCARETSData.City)="tp"))
    GROUP BY tblCARETSData.ClosingDate
    HAVING (((tblCARETSData.ClosingDate)>=#1/1/2011# And (tblCARETSData.ClosingDate)<=#1/31/2011#));

    Also - is there a easy method to insert the HAVING date ranges of these three queries so I don't have to hard code them each month before I run them?

    Thanks . . . Rick

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Okay this works:


    SELECT Count(tblCARETSData.City) AS CountOfCity
    FROM tblCARETSData
    WHERE (((tblCARETSData.ClosingDate)>=#1/1/2011# And (tblCARETSData.ClosingDate)<=#1/31/2011#))
    HAVING (((tblCARETSData.City)="bd" Or (tblCARETSData.City)="cthc" Or (tblCARETSData.City)="coa" Or (tblCARETSData.City)="dctr" Or (tblCARETSData.City)="dhs" Or (tblCARETSData.City)="ds" Or (tblCARETSData.City)="iwld" Or (tblCARETSData.City)="iw" Or (tblCARETSData.City)="ind" Or (tblCARETSData.City)="lq" Or (tblCARETSData.City)="mec" Or (tblCARETSData.City)="mntc" Or (tblCARETSData.City)="pdst" Or (tblCARETSData.City)="pspr" Or (tblCARETSData.City)="rm" Or (tblCARETSData.City)="salt" Or (tblCARETSData.City)="sltb" Or (tblCARETSData.City)="th" Or (tblCARETSData.City)="tp"));

    Now I need the column header to read ClosedSales.

    After that I suppose I can then create a UNION ALL query for the remaining two queries.

    Yes?

    Thanks Rick

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my sweet $deity, access sql syntax is butt ugly

    here's your query with the insanity removed, a second query added in a union, and a rowtype column added --
    Code:
    SELECT 'ClosedSales' AS rowtype
         , COUNT(*) AS amount
      FROM tblCARETSData
     WHERE ClosingDate BETWEEN #1/1/2011# 
                           AND #1/31/2011#
       AND City IN ('bd','cthc','coa','dctr','dhs'
                   ,'ds','iwld','iw','ind','lq'
                   ,'mec','mntc','pdst','pspr','rm'
                   ,'salt','sltb','th','tp')
    UNION ALL
    SELECT 'SomethingElse' AS rowtype
         , COUNT(*) AS amount
      FROM someothertable
     WHERE something = 'somevalue'
    you wanted column headers but they have to be row headers instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    Also - is there a easy method to insert the HAVING date ranges of these three queries so I don't have to hard code them each month before I run them?
    yes, you can set up a formula based on the current date

    is it always the previous calendar month?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Almost except . . .

    The year remains constant until January of each year when I'm working on December of the previous year.

    qry Code for my NewListings, NewPendings and ClosedSales will always have a BETWEEN date for the previous month.

    I now have 4 separate queries that need the BETWEEN date range. One each for a county and then the Desert one which queries by the 19 separate city codes.

    That equates to changing the dates 12 times for the three statuses above. Dangerous - subject to error!

    I could insert [parameter query] but then I still must enter the dates 12 times.

    I saw your code using SERIALDATE but don't fully understand how and where.

    This looks like the best solution.

    Thanks Rick

Posting Permissions

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