What am I missing?

[eyal8r]

When I run this Query without the WHERE statement/Clause- it returns the accurate 985 records. However, when I include the criteria with the WHERE clause (it takes a range of +/- 10% of the Square Footage size into consideration), I lose 15 records, and only get 970 results. When I increase the range to +/- 15% in that field, it gives me 7 more records back.

Obviously it is dropping out records that do not meet the criteria for that field- how do I prevent that? I guess I would like it to display a ZERO or something, but still display the entire 985 records. What do I need to include to do that? Here's the SQL-

SELECT tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, Avg(tbSold.SoldPrice) AS AvgOfSoldPrice, Count(tbSold.SoldPrice) AS CountOfSoldPrice
FROM tbListing AS tbAct, tbListing AS tbSold
WHERE (((tbSold.SqFt) Between ([tbAct].[SqFt]+([tbAct].[SqFt]/100*10)) And ([tbAct].[SqFt]-([tbAct].[SqFt]/100*10))))
GROUP BY tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, tbSold.Status, tbSold.Area, tbSold.Level
HAVING (((tbAct.Status)="ACT") AND ((tbAct.City)="TEMPE") AND ((tbSold.Status)="CLOSD") AND ((tbSold.Area)=[tbAct].[Area]) AND ((tbSold.Level)=[tbAct].[Level]));

 

[eyal8r]

Basically- it's dropping the records out that do not have any records from tbSold that meet the +/- 10% criteria in the Sq Ft range. So, it's dropping those out of the results. What I'd like it to do is to still display those records, but include 0s in those tbSolds fields. Is there a way to do this?

 

[The_Doc_Man]

Well...

This sounds like a "have my cake and eat it, too" scenario. The short answer is no. But then there is Doc's longer answer...

You have a criterion (component of WHERE clause) that limits what you show. You are complaining that when the record fails your test, it does exactly what you told it to do. I.e. NOT select the record when the WHERE clause is NOT met.

So...

Build a UNION query that starts with your query - and then reverses the WHERE clause to pull in those items that were not shown as the second leg of the UNION. You can look up UNION queries in Access Help. Basically, a UNION query just tacks two queries together. It works when the two tacked-together queries return the same fields or the same field types. Since this is just two queries from the same table, just different filters, it should be a piece of cake to meet this latter requirement.