New to Access (Need a Union I think)

  • Thread starter Sammy Hoelscher via AccessMonster.com
  • Start date
S

Sammy Hoelscher via AccessMonster.com

I'm working on a database. I import listings of house sold in different
subdivisions daily along with new listing. I'm trying to get a query that
will look at each new listing and tell me from looking at the sold table
that houses in X subdivision are selling for Y dollars a sq. ft. so the new
house should sell for Z.

Sold Table
Address
Subdivion
SoldPrice
SqFt

NewListing Table
NewListing_Address
NewListing_Subdivion
ListPrice
NewListing_SqFt

I want a query that returns
NewListing_Address
NewListing_SqFt
ListPrice
AveragePriceSqFtofSubdivision
MarketValue (AveragePriceSqFtofSubdivision * NewListing_SqFt)

Please help
 
C

Carl Rapson

Try this:

1. Create a query ("AveragePriceSqFtofSubdivision") with the following SQL
statement:

SELECT DISTINCT Subdivision, AVG([Sell Price]/[Sq Ft]) AS
AveragePriceSqFt
FROM Sold
GROUP BY Subdivision;

2. Create another query ("Market Value") with the following SQL statement
(watch the formatting):

SELECT NewListing.Address, NewListing.[Sq Ft], NewListing.[List Price],
Format((AveragePriceSqFtofSubdivision.AveragePriceSqFt*NewListing.[Sq
Ft]),"Currency") AS [Market Value]
FROM NewListing INNER JOIN AveragePriceSqFtofSubdivision ON
NewListing.Subdivision=AveragePriceSqFtofSubdivision.Subdivision;


3. The query [Market Value] should return what you're looking for.

HTH,

Carl Rapson
 
Top