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