Subquery question

[dth122]

I'm having a problem rolling a subquery into a query.



I have two tables, tblAllCells and tblPlots. tblPlots is a subset of tblAllCells. They linked by SiteNumber.

I have a subquery that operates on tblPlots with an IIf that sets a value "Status" based on the existence of various values in the fields of tblPlots. It sets the Status to "A" if none of the conditions are met. This is working fine. (so far)

subqryStatus:
SELECT tblPlots.SiteNumber, IIf([TakerNum]>0,"O",IIf([tblPlots.OwnerNum]>0,"S",IIf([HoldFor] Is Not Null,"H","A"))) AS Status
FROM tblOwners RIGHT JOIN (tblPlots LEFT JOIN qryUsage ON tblPlots.SiteNumber = qryUsage.SiteNumber) ON tblOwners.OwnerNum = tblPlots.OwnerNum
ORDER BY tblPlots.SiteNumber;




I then have a main query where I join the subquery and tblAllCells. I'm looking for a result that includes all of the SiteNumbers from tblAllCells, with the Status from the subquery for any matching SiteNumbers in tblPlots and a null Status for any SiteNumbers that are in tblAllCells but not in tblPlots. (though I would also take some other status if it could set it)

qryStatus:
SELECT tblAllCells.SiteNumber, subqryStatus.Owner, subqryStatus.Usage, subqryStatus.HoldFor, subqryStatus.Status
FROM tblAllCells LEFT JOIN subqryStatus ON tblAllCells.SiteNumber = subqryStatus.SiteNumber;


I'm getting the correct rows from tblAllCells, but I'm seeing a status of "A" (my "else" path from the subquery) for the SiteNumbers in tblAllCells that aren't in tblPlots. What I expect is a null.

I'm baffled as to how the IIf expression in the subquery can operate on rows of tblAllCells when it's not even included in the subquery.

What am I missing?
- Dave

[ranman256]

I advise not to use IIFs in the query. instead, use a lookup table joined to the data table.
this performs the IIF.

[Micron]

Not sure the IIF is relevant to the problem or not, but one thing you can do is make a table from that query and examine all the data for issues. Then join that table to your main query and play with the results. What that will do is either confirm or eliminate the possibility that the issue is the expression. It could also be something as simple as not using the right join, but then again we have no results vs input to look at.
FYI - what you are describing is a nested query. A sub query is something else all together.