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!

 

[jrbarnett]

You need to use two copies of the same table but with aliases.
One for your property, one for the details of others around.
For example:

select otherhouses.price, otherhouses.address, otherhouses.area, otherhouses.stories
from properties thishouse inner join properties otherhouses
on thishouse.propertyid <> otherhouses.propertyid
where thishouse.house_id = 123456 and
 thishouse.area = otherhouses.area and
 thishouse.stories = otherhouses.stories and
 otherhouse.status = 'Active'

Basically, you just need to refine the query to add in extra criteria as needed.

John

 

[eyal8r]

ok- First off- thanks for the response! You have totally lost me. I didn't know you can do alias' in Access. I have 2 queries setup- one for Actives, one for Solds. I am trying to reference fields from each query to yank the average- but it's just not working. Would you mind giving me more details on how to do this? I REALLY appreciate it!

 

[eyal8r]

Or, should I break teh data into different tables all together to make it easier/faster?
1 for Solds, 1 for Actives?

 

[jrbarnett]

Can you post your SQL and I'll have a look tomorrow (am UK based).

John

 

[eyal8r]

I could- but it's just all soo messed up, I dont know if it's worth looking at. I've been trying multiple things, but I clearly have no clue what I'm doing. Been reading up on Alias'- I get what they are, and I think how to set them up. Now- Physically, I don't know how to use them in the context here. I don't quite understand what the Join does either- so gotta figure that out too. Any other hints you can give?

 

[Remou]

Something like:

[tt]SELECT DAvg("Price","Houses","Level=" & [Level] & " AND Area=" & [Area] & " AND [Year] BETWEEN " & [Year]-10 & " AND " & [Year]+10 & " And SF Between " & [SF]-([SF]/100*15) & " And " & [SF]+([SF]/100*15)) AS AvgPrice, Houses.ID, Houses.Address, Houses.Status, Houses.SF, Houses.Year, Houses.Level, Houses.Area, Houses.Price
FROM Houses;[/tt]

 

[eyal8r]

Remou-
Awesome! Can you help explain it to me, so I can learn what this is doing?

 

[eyal8r]

Actually, I dont think this will work. You have to do this for each record in the table with an 'Active' status on it- and compare it to each record in the same table with a SOLD status on it. I don't see where it's doing that- am I missing it?

 

[Remou]

It is not so difficult to add status.

SELECT DAvg("Price","Houses","Status = 'Sold' AND Level=" & [Level] & " AND Area=" & [Area] & " AND [Year] BETWEEN " & [Year]-10 & " AND " & [Year]+10 & " And SF Between " & [SF]-([SF]/100*15) & " And " & [SF]+([SF]/100*15)) AS AvgPrice, Houses.ID, Houses.Address, Houses.Status, Houses.SF, Houses.Year, Houses.Level, Houses.Area, Houses.Price
FROM Houses
WHERE Houses.Status="Active"

 

[eyal8r]

ok- I think I'm getting this. Read up on Joins and whatnot- I understand the alias thing now...

How do I do the JOIN <> property in Access? It gives me 3 options- none of which seem to be 'left side does not equal right side'?

 

[Remou]

For the most part, it is better to work in SQL view.

Here are some more references:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

However, my example makes use of the domain aggregate function DAvg, rather than using sub-queries.

 

[eyal8r]

ok- so basically Access is telling me that it cannot use the <> statement in Design view on my Join Query. So, I have the opposite- '=' in place- but it won't run the results that way (well, it comes up null). So, before running the results- I have to change the = to the <> in the SQL statement- then it runs for a bit and pops up results. I THINK it's working!

 

[eyal8r]

Yeah, I can see where you're coming from on that, Remou- the problem is a newbie like me gets overwhelmed in SQL view. I'm trying to use Design View- then go to SQL to figure out the proper syntax of it all. I'm only processing 1/10th of the entire DB- and it's taking a little bit to generate these basic #s. So- I'm gonna have to find ways to break it down so it doesn't set my processor on fire! LOL...

 

[Remou]

Have you tried my example? It does not use joins.

 

[eyal8r]

I was trying to figure out both, actually. Can you explain how I would do yours in the Design View? I know it's not the best place to do it- but I learn fast when I can visually see it...

How does yours not use joins, while the other won't work without it?

 

[Remou]

Two different approaches. Paste the example above into SQL view, check the field names, and then switch to design view. You may wish to read about Domain Aggregate Functions.

 

[eyal8r]

Remou-
ok- so to explain your example- you're saying...
Select the Average of (*THESE FIELDS WITH THIS CRITERIA ATTACHED TO THEM) and place it in a Field called AvgPrice... is that right? What's the rest of the example? I'm just trying to learn here.
thanks a ton!
BTW- here's the SQL statement I have from Design View- but not including YearBuilt or SqFt range (yet). The TableName is long but I will change it later- it was a temp name. LOL

SELECT tbActives.MLS, tbActives.Status, tbActives.Address, tbActives.Area, tbActives.Level, tbActives.ListPrice, tbActives.YearBuilt, Avg(tbSolds.SoldPrice) AS AvgOfSoldPrice
FROM tbListingsRenameColumns AS tbActives INNER JOIN tbListingsRenameColumns AS tbSolds ON tbActives.ID <> tbSolds.ID
GROUP BY tbActives.MLS, tbActives.Status, tbActives.Address, tbActives.Area, tbActives.Level, tbActives.ListPrice, tbActives.YearBuilt, 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 Avg(tbSolds.SoldPrice);

 

[eyal8r]

ok- didn't do anything. Here's what I have, using your stuff and substituting out the names...

SELECT DAvg("SoldPrice", "Status = 'CLOSD' AND Level=" & [Level] & " AND Area=" & [Area] & " AND [YearBuilt] BETWEEN " & [YearBuilt]-10 & " AND " & [YearBuilt]+10 & " And SF Between " & [SqFt]-([SqFt]/100*15) & " And " & [SqFt]+([SqFt]/100*15)) AS AvgPrice, tbListingsRenameColumns.ID, tbListingsRenameColumns.Address, tbListingsRenameColumns.Status, tbListingsRenameColumns.SqFt, tbListingsRenameColumns.YearBuilt, tbListingsRenameColumns.Level, tbListingsRenameColumns.Area, tbListingsRenameColumns.ListPrice
FROM tbListingsRenameColumns
WHERE tbListingsRenameColumns.Status="Active"

I ran it- and it just popped open a datasheet with no rows in it.

 

[eyal8r]

BTW- I tried JRBarnett's example with out any joins at all- and it seemed to have worked... not sure if the data is correct or not- but it's running!

Can this be right?