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

    Unanswered: Count With Date Range and Price Range

    This is the query that the Wizard put together: (Access 2003 on Win XP Pro SP3)

    SELECT DISTINCTROW Count(*) AS [Count Of tblCARETSData]
    FROM tblCARETSData
    GROUP BY tblCARETSData.ClosingDate, tblCARETSData.ClosePrice, tblCARETSData.County
    HAVING (((tblCARETSData.ClosingDate)>=#6/1/2009# And (tblCARETSData.ClosingDate)<=#6/30/2009#) AND ((tblCARETSData.ClosePrice)<=199999) AND ((tblCARETSData.County)="orange"));


    Is stead of saying Count = 282 it list 282 rows!

    Removing DISTINCTROW changes nothing, it's the same result.

    My goal is then to create UNION ALL querys for 20 different price ranges.

    Or am I on the wrong track?

    Thanks

    Rick

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What was your thought process with DISTINCTROW there?

    I'm also a fan of creating tables that define the ranges you want to break down so you don't have to hardcode 20 different price ranges. You then get all 20 ranges in one query and can use a WHERE clause to filter it down further if need be. That worked best for me when I was routinely creating regional housing supply charts sourced on RETS data...
    oh yeah... documentation... I have heard of that.

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

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

    Count With Date Range and Price Range

    Hi Teddy . . .

    The DISTINCTROW was placed there by MS query Wizard.

    I could just place all of my data in one table but how would I program each price range and COUNT for each price range in one query?

    Are you in real estate Teddy?

    Thanks.

    Rick

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DISTINCTROW, in every instance where i have seen it, is useless

    your problem of the repeated counts in the result set is due entirely to your use of the GROUP BY clause

    to say nothing of the HAVING clause abomination

    once again, the Access Query Design View shafts another punter

    here, try this and see if you like the results --
    Code:
    SELECT COUNT(*) AS [Count Of tblCARETSData]
      FROM tblCARETSData
     WHERE ClosingDate >= #6/1/2009# 
       And ClosingDate <= #6/30/2009#
       AND ClosePrice <= 199999
       AND County = 'orange'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Rick Schreiber
    Hi Teddy . . .

    The DISTINCTROW was placed there by MS query Wizard.
    I agree with Rudy, I have yet to see a realistic application of DISTINCTROW. I say ditch it.


    I could just place all of my data in one table but how would I program each price range and COUNT for each price range in one query?
    Say you've got a table for price ranges:

    PriceRanges
    -----------
    price_range_id
    friendly_name
    start_price
    end_price


    You can then join on that like so:

    SELECT PriceRanges.friendly_name, count(*)
    FROM RETS INNER JOIN PriceRanges ON RETS.close_price >= PriceRanges.start_price AND RETS.close_price < PriceRange.end_price
    GROUP BY PriceRanges.friendly_name

    Are you in real estate Teddy?

    Thanks.

    Rick
    Indeed I am.
    Last edited by Teddy; 07-30-09 at 19:46. Reason: >= != =>
    oh yeah... documentation... I have heard of that.

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

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

    Count With Date Rante Price Range

    Hi Teddy - I don't have a price range table.

    I download directly from the CARETS server onto my computer.

    This way I can accomplish ALL of my data queries off line against one table: tblCARETSData.

    I have some pretty big downloads sometimes. xxx,xxx

    It's easer I think to query against one table for all of my needs in lieu of making tables all of the time to satisfy many queries. Am I out to lunch here?

    I've tried parsing together your suggestion involving the RETS server but I'm unable to make the transition from your table/field names to my table/field names.

    I have completed a UNION ALL query using Rudy's suggestion but of course there is no label for each price range. Logically they are in order but I'm sort of (get this) dyslexic and transpose things at times. Ha!

    Suggestions . . ?

    Thanks much . . . to you and Rudy.

    Rick

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Forget that I mentioned a RETS server. DMQL does not allow for any concept of joins. RETS2 does, but that's a whole different animal that is WELL outside the scope of this conversation...

    Here's the deal... You're either going to need to hardcode price ranges, or you'll need to store a table that defines your price ranges. If you hardcode them (which is what you're doing now), you will be able to get the job done but you will have to recode all of your queries, reports and forms by hand anytime those ranges need to be altered or supplemented. If you use a "lookup" table that gives you the start and end price ranges that have meaning, you can then simply update one table to modify or add a new range and not have to worry about anything else breaking.

    Clearly I favor the second approach.


    Also, I generally find it's better to pursue the most flexible solution as opposed to the most compact. You simply don't know what business questions you'll need to ask of your application in the future so it's best to build in a reasonable degree of flexibility right from the start. With this approach, keeping around a small table that defines all of the price ranges that are important to you seems like it will save you a good deal of work in the future...
    oh yeah... documentation... I have heard of that.

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

Posting Permissions

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