Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Count Date Range Occourances

    SELECT Count(tblSoCalMLS_Download.LISTDATERECEIVED) AS CountOfLISTDATERECEIVED
    FROM tblSoCalMLS_Download
    HAVING (((Count(tblSoCalMLS_Download.LISTDATERECEIVED)) Between #5/1/2007# And #5/31/2007#));

    Returns NOTHING!

    If I replace HAVING with WHERE I get the dreaded

    Cannot have aggregate Function in a WHERE clause error.

    Any ideas?

    Thanks .. . Rick

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

    Figuered it out.

    SELECT Count(tblSoCalMLS_Download.LISTDATERECEIVED) AS CountOfLISTDATERECEIVED
    FROM tblSoCalMLS_Download
    HAVING (((tblSoCalMLS_Download.LISTDATERECEIVED) Between #5/1/2007# And #5/31/2007#));

    This returns the correct count!

    Thanks .. . Rick

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    read very carefully at what you're asking to Access to do in that having clause...

    Having THE COUNT OF LISTDATERECEIVED between date1 and date2

    Edit: Try changing that to a where clause now too...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

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

    No difference in change replacing WHERE and HAVING

    The following is what I really wish to accomplish.


    SELECT Count(tblSoCalMLS_Download.LISTDATERECEIVED) AS CountOfLISTDATERECEIVED,
    Count(tblSoCalMLS_Download.PENDINGDATE) AS CountOfPENDINGDATE,
    Count(tblSoCalMLS_Download.CLOSEDDATE) AS CountOfCLOSEDDATE
    FROM tblSoCalMLS_Download
    WHERE (((tblSoCalMLS_Download.LISTDATERECEIVED) Between #5/1/2007# And #5/31/2007#)
    AND ((tblSoCalMLS_Download.PENDINGDATE) Between #5/1/2007# And #5/31/2007#)
    AND ((tblSoCalMLS_Download.CLOSEDDATE) Between #5/1/2007# And #5/31/2007#));

    This produces the same count for each of the three fields; 29!

    It should produce numbers in the 2,000 range for each field.

    There are no error messages.

    Maybe I should port this over to VBA?

    Is there a way to pass one date range to all three fields at one time? It will always me the same, a monthly date range.

    Thanks . . . Rick

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber
    This produces the same count for each of the three fields; 29!
    of course, and this is because none of the fields you are counting is NULL (which is due entirely to the WHERE clause, which ensures that they aren't)

    you need to understand three important concepts

    1) COUNT, as well as other aggregate columns like SUM, MIN, etc, ignore NULLs

    2) all columns in the same query will return exactly the same count provided that none of the values are NULL

    3) the WHERE clause applies to all rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    NULL records

    Rudy - you said I have to undertand something. . . .

    " . . .of course, and this is because none of the fields you are counting is NULL (which is due entirely to the WHERE clause, which ensures that they aren't)"

    What I don't understand is why I want to count NULL values in the field? It either has a matching date within the date range or it doesn't.

    I want to count every "date match" in the date range provided.

    I'm at a loss as to why I'm even considering NULL values? (Obviously my problem!)

    I swithced the WHERE clause with the HAVING clause and there was no change in the results for the three fields, 29!

    I would rather learn from you this time Rudy as opposed to asking you to provide me with the code/syntax.

    Thanks Much.

    Rick

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, by way of illustration, please count the number of As, Bs, and Cs in this example:
    Code:
    A  B  C
    9  1  5
    3  8  2
    7  3  3
    2  4  1
    4  5  6
    5  6  9
    6  7  8
    8  2  7
    1  9  4
    if you count them correctly, there should be 9 of each

    correct?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rick seriously - I think this is the third time I have said this. I promise it will now be the last.

    Please, please, please take some time to work through some SQL tutotials. You've been posting on here for quite some time now but are still not getting the fundamentals. You are not a dim fella so I can only assume that are just not doing any of the ground work that you seriously need to cover if you are going to write involved, or for that matter basic, SQL. Reading your first post was like groundhog day.

    http://www.sql-tutorial.net/
    http://www.firstsql.com/tutor.htm
    http://www.w3schools.com/sql/default.asp
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    A  B  C
    9  1  5
    3  8  2
    7  3  3
    2  4  1
    4  5  6
    5  6  9
    6  7  8
    8  2  7
    1  9  4
    I see one A, one B and one C

    Do you not have to have a GROUP BY clause when using HAVING, or am I missing a fine point here?
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Rick Schreiber
    SELECT Count(tblSoCalMLS_Download.LISTDATERECEIVED) AS CountOfLISTDATERECEIVED
    FROM tblSoCalMLS_Download
    HAVING (((tblSoCalMLS_Download.LISTDATERECEIVED) Between #5/1/2007# And #5/31/2007#));

    This returns the correct count!

    Thanks .. . Rick
    ..but have you appreciated why it didn't work first time round
    the SQL engine is dumb it will only return what you ask it.
    it isn't a politician that will give you the answer it knows you want
    it isn't a salesman that will give you the answer it thinks you want
    it isn't an estate agent... but then agisn you'd knwo all about that......
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Do you not have to have a GROUP BY clause when using HAVING, or am I missing a fine point here?
    Nope. The reason you are probably thinking this is because there is virtually no time you would ever want a having clause without a group by.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    Nope. The reason you are probably thinking this is because there is virtually no time you would ever want a having clause without a group by.
    So why would we in this case?
    I'm just not quite following yet - humour me
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    So why would we in this case?
    we wouldn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Code:
    A  B  C
    9  1  5
    3  8  2
    7  3  3
    2  4  1
    4  5  6
    5  6  9
    6  7  8
    8  2  7
    1  9  4
    I see one A, one B and one C
    smartass

    rick, when you get around to this thread, the answer is "nine each"

    moving on to the second step of my illustration, imagine that A is LISTDATERECEIVED, B is PENDINGDATE, and C is CLOSEDDATE, and that instead of numbers, those columns contained dates

    now how many dates are in each column? the answer, again, is 9

    now pretend that the results came from your query

    can you see now why there were 29 of each?

    okay, but why 29, and not the 2000 you were expecting?

    because your WHERE clause pulled out only those rows where all three dates were in the same month
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    I'm just not quite following yet - humour me
    Just now or from this point on?

    Rudy is right - we wouldn't. Isn't that where it all started? But you could if you wanted - really it would become some sort of existence check. Perhaps (T-SQL):
    Code:
    IF EXISTS(SELECT COUNT(*) FROM MyTable HAVING COUNT(*) > 10) BEGIN....
    to continue if there are 11+ records in the table. Not 100% certain it would work but you see what I mean?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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