This is the query that the Wizard put together: (Access 2003 on Win XP Pro SP3)
SELECT DISTINCTROW Count(*) AS [Count Of 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.
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...
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?
Indeed I am.
Last edited by Teddy; 07-30-09 at 19:46.
Reason: >= != =>
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!
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...