Help w/ Query- Reference a Specific Record?

[eyal8r]

Hey guys-
Been trying to figure this out, but am stuck. Obviously I'm fairly new to Access...

I have a table of properties and the price those properties sold for. Each property is in a specific area of town. I want to take each record and display the Address, Area of town, Price it sold for, and then the AVERAGE PRICE of all the other properties that sold in that same, specific area (not all the other areas of town, but the same area that the property sold for). The result should be displayed as below-

-Address- -Area- -Sold Price- -Avg Sold Price in Area-


And then it will list every property in the database, each with the same calculation all in one grid/table/dataset.

I figured out how to write a query to average all the sold prices in each area- but I don't know how to display that in conjunction with each record. Can someone throw a hint at me here?
Thanks

 

[Ron Weston]

First of all, switch the order of display: area, address, sold price
Second, you will have to use a report, where the data is grouped by area
after the properties of each area are printed, use a footer to print
the number of properties: count([address])
the total prices for all those properties: sum([sold price])
and the average selling price: sum([sold price])/count([address])
hope this helps - Ron

 

[eyal8r]

Ron-
Not saying you're wrong by any means- but- I'm not sure a report is what I'm looking for. Eventually, this basic query will be fairly complex- limiting it down to a size range, year range, whether or not the property is 1 or 2 stories, etc etc. Then I need to be able to access the results in a form. So- I was trying to get the very basic format together so that I can learn and expand the queries one step at a time. I just don't know how to write a query that references it's own record, runs a calculation and then displays it all. Does that make sense? Is there another way to accomplish this?

 

[Ron Weston]

There may be other ways to do it, but not, I think, simply by using queries. I've been doing this a long time, but I still don't know everything ... by a long shot. This is the way I would do it.

Of course, if you want this to appear in a form, you might write some VBA code to process the data, create a table, and then present that table in a form, but that would also be much more than a query could do.

 

[ajetrumpet]

I want to take each record and display the Address, Area of town, Price it sold for, and then the AVERAGE PRICE of all the other properties that sold in that same, specific area The result should be displayed as below-
-Address- -Area- -Sold Price- -Avg Sold Price in Area-

I figured out how to write a query to average all the sold prices in each area- but I don't know how to display that in conjunction with each record.

You're only one step away...now just write another SELECT and include the field of the other query with the AVG number in it. You will end up with a list of records with one field having the same number listed over and over again (the field listing the avg price), but isn't that what you want?? (To compare the prices I mean...)

 

[eyal8r]

YES! THAT WAS IT (I think!)- it SEEMS to be working! Here's the SQL-
SELECT tbTempImport.ADDRESS, tbTempImport.AREA, tbTempImport.ST, tbTempImport.SP, qryAvgSoldinArea.AvgOfSP
FROM tbTempImport, qryAvgSoldinArea
WHERE (((tbTempImport.ST)="CLOSD"));

HOWEVER- I have a bunch of repeated records. I'm not sure if it's a problem in my data, or, the way I have the Query written is causing it. Any ideas?
Thank you!!! That's awesome how you can do that!

 

[eyal8r]

Yeah, it's repeating each record about 11 times... but then each group of 11 is repeated throughout the results- it's very odd what's going on. Any ideas?

 

[ajetrumpet]

how 'bout the SQL of the first query....???

 

[neileg]

Yeah, it's repeating each record about 11 times... but then each group of 11 is repeated throughout the results- it's very odd what's going on. Any ideas?

Exciting! That's called a cartesian product and usually comes from an incorrect join between your tables. As ajetrumpet says, post your SQL.

 

[eyal8r]

Well- I made a simple db to work with as an example. I've been playing around with it- and THINK I figured out a way to do it. I made a simple query first that takes the average of all the areas. I then created a 2nd query that uses THAT expression, and then included a Criteria like this:
[tbPrices].[Area]=[AvgofAreas].[Area]

That SEEMS to be doing it now. I don't know if it's the 'proper' way to do thing- but it's working... I'll try it on my REAL db here now and see if I can get it to work. I guess nested Queries is the way to go. I know I'm going to have multiple criteria for this- so I hope I can nest multiple queries together...
I'll be back...

 

[eyal8r]

ok- FYI-
SELECT tbTempImport.ADDRESS, tbTempImport.AREA, tbTempImport.ST, tbTempImport.LP, tbTempImport.SP, qryAvgSoldinArea.AvgOfSP
FROM tbTempImport, qryAvgSoldinArea
WHERE (((tbTempImport.ST)="ACT"));

and the other-

SELECT tbTempImport.AREA, tbTempImport.ST, Avg(tbTempImport.SP) AS AvgOfSP
FROM tbTempImport
GROUP BY tbTempImport.AREA, tbTempImport.ST
HAVING (((tbTempImport.ST)="CLOSD"))
ORDER BY Avg(tbTempImport.SP);


Do you see the problem in here?

 

[eyal8r]

I GOT IT!!!! Here's what I got-
This query finds the average price of each property in every area...
SELECT tbTempImport.AREA, tbTempImport.ST, Avg(tbTempImport.SP) AS AvgOfSP
FROM tbTempImport
GROUP BY tbTempImport.AREA, tbTempImport.ST
HAVING (((tbTempImport.ST)="CLOSD"))
ORDER BY Avg(tbTempImport.SP);


Then- I nested into this query- which simply displays every property's address, the Area the property lies within, the status category (CLOSD), that property's sold price, and then the Previous query's result for that specific category average price...
SELECT tbTempImport.ADDRESS, tbTempImport.AREA, tbTempImport.ST, tbTempImport.SP, qryAvgSoldinArea.AvgOfSP
FROM tbTempImport, qryAvgSoldinArea
WHERE (((tbTempImport.AREA)=[qryAvgSoldinArea].[AREA]) AND ((tbTempImport.ST)="CLOSD"))
ORDER BY tbTempImport.AREA, tbTempImport.SP;


It seems to be working for me- Am I doing this right?

 

[ajetrumpet]

I GOT IT!!!!

SELECT tbTempImport.ADDRESS, tbTempImport.AREA, tbTempImport.ST, tbTempImport.SP, qryAvgSoldinArea.AvgOfSP
FROM tbTempImport, qryAvgSoldinArea
WHERE (((tbTempImport.AREA)=[qryAvgSoldinArea].[AREA]) AND ((tbTempImport.ST)="CLOSD"))
ORDER BY tbTempImport.AREA, tbTempImport.SP;

I think this is what you were looking for. All this does is give you the extra column of "average" you were looking for in the beginning....

You're only one step away...now just write another SELECT and include the field of the other query with the AVG number in it.

Congrats!!

 

[eyal8r]

Thank you! I'm having fun learning this stuff while I go! I appreciate all the help - sincerely...