Desperate- Complex Query Help

[eyal8r]

ok guys-
I am struggling here. I've been trying to figure this out, got a small portion of it completed- run the query and I run out of Temp disk space- so it never gives results.

Basically- I have all the property data in one table. The goal is to find the how a property listed for sale compares to all those properties similar to it have sold for in the past x amount of months. I want to compare it to all the other houses in the same area (these are Sold status). The Sold houses have to have the same number of levels (stories), be in the same area (Area) of town, have sold in the last x amount of months (eventually I will compare the houses that have sold int eh past 3, 6, 9, and 12 month time frames), and have a square footage range of +/- 15%.

For example-
There's a house for sale (Active status) for $200,000. It is 2,000 square feet, built in 1995, has 1 level, and is in Area 510. I want to compare it to all the other houses in the same area, similar to it- which means, +/- 15% of the Square Footage (1700-2300), in Areas 510, has only 1 level, and was built in +/- 10 years (1985-2005). I then need to take the average $/sqft of THOSE houses, and compare it to my one active house. Let's say there's 10 houses matching that description, and the Avg $/sf comes out to $240,000 on average for those 10 houses.

So- the final result will be:
123 Main St Active 2,000sf 1995 1 (level) 510 (Area) $200,000 $240,000
And then a calculation of Asking Price divided by the Avg Sold Price of Similar houses which, = 83.3%

All the data/fields are in one table. I have been trying to work nested queries- but, I've only got 2 or 3 specs in, and it runs out of space on my temp disc. So, I believe I'm building it wrong. Can someone please help me out to figure out how to accomplish this? I've got more to add onto it- but- I think if I can get this part figured out, I can do the rest on my own...
Thanks a ton guys!

 

[eyal8r]

ok- here's what I have-
SELECT tbActives.MLS, tbActives.Status, tbActives.Address, tbActives.Area, tbActives.Level, tbActives.YearBuilt, tbActives.SqFt, tbActives.ListPrice, Avg(tbSolds.SoldPrice) AS AvgOfSoldPrice
FROM tbListingsRenameColumns AS tbActives, tbListingsRenameColumns AS tbSolds
GROUP BY tbActives.MLS, tbActives.Status, tbActives.Address, tbActives.Area, tbActives.Level, tbActives.YearBuilt, tbActives.SqFt, tbActives.ListPrice, tbSolds.Status, tbSolds.Area, tbSolds.Level
HAVING (((tbActives.Status)="ACT") AND ((tbSolds.Status)="CLOSD") AND ((tbSolds.Area)=[tbActives].[Area]) AND ((tbSolds.Level)=[tbActives].[Level]))
ORDER BY tbActives.Area, tbActives.Level, tbActives.SqFt, tbActives.ListPrice;

This DOES run and appears to work (I haven't verified the #s yet- but seems to be right). However- I am stuck and have a couple questions.

1) I need to incorporate the SqFt range of +/- 15%. So, it will be something like this-
[tbSolds].[SqFt] BETWEEN ([tbActives].[SqFt]-([tbActives].[SqFt]/100*15) AND ([tbActives].[SqFt] + ([tbActives].[SqFt]/100*15)).
---> Where do I put this, and is is right?

2). As of right now, it is taking this 30 seconds or so to run the existing Query. I have more criteria to add to the query before it's done (sqft, Year range, pool, etc). On top of that- this is only about 1/10th of the records that I will be processing when it's all said and done. I'm concerned about performance here. I can trim the ultimate usage down by searching by City- but- I believe there will still be quite a bit of required processing each time it's run. How do you suggest I handle this?

THANK YOU GUYS!!!

 

[eyal8r]

#1- here's what I got. I manually checked the #s on it- appears to be right...

SELECT tbActives.MLS, tbActives.Status, tbActives.Address, tbActives.Area, tbActives.Level, tbActives.YearBuilt, tbActives.SqFt, tbActives.ListPrice, Avg(tbSolds.SoldPrice) AS AvgOfSoldPrice, Count(tbSolds.SoldPrice) AS CountOfSoldPrice
FROM tbListingsRenameColumns AS tbActives, tbListingsRenameColumns AS tbSolds
WHERE (([tbSolds].[SqFt] Between ([tbActives].[SqFt]+([tbActives].[SqFt]/100*15)) And ([tbActives].[SqFt]-([tbActives].[SqFt]/100*15))) AND ([tbSolds].[YearBuilt] Between ([tbActives].[YearBuilt]+10) And ([tbActives].[YearBuilt]-10)))
GROUP BY tbActives.MLS, tbActives.Status, tbActives.Address, tbActives.Area, tbActives.Level, tbActives.YearBuilt, tbActives.SqFt, tbActives.ListPrice, tbSolds.Status, tbSolds.Area, tbSolds.Level
HAVING (((tbActives.Status)="ACT") AND ((tbSolds.Status)="CLOSD") AND ((tbSolds.Area)=[tbActives].[Area]) AND ((tbSolds.Level)=[tbActives].[Level]))
ORDER BY tbActives.Area, tbActives.Level, tbActives.SqFt, tbActives.ListPrice;


Still needing an answer to #2 tho...

 

[eyal8r]

ok- all is working greatly! Even got forms throwing in values within this Query! WOW this is AWESOME STUFF!!!

I am concerned about the performance, however. Anyone with any ideas to improve it?