Unexpected query results

[Isaac]

I must be really losing it today. Here is my scenario:

1. In qryID_and_BU_CompleteStatus, if I run that query, the column BU - has NO records where the value is 214
2. In the higher-level query (which refers to #1 query), called qryID_and_BU_Completion_1, I am querying from two sources: SharepointIDs_to_BusinessUnits and qryID_and_BU_CompleteStatus, left joined from SharepointIDs_to_BusinessUnits.[BusinessUnitID] to qryID_and_BU_CompleteStatus.[BU]
3. In that #2 query, I am returning the column [Complete] from qryID_and_BU_CompleteStatus
4. On the record in question, where the SharepointIDs_to_BusinessUnits.[BusinessUnitID] = 214, the [Complete] column is showing a value, rather than Null.

How is this possible?

SQL for lower level query:

SELECT qryID_and_BU.SP_ID, CInt([qryID_and_BU].[BU]) AS BU, IIf(IsNull([qryRecordsWithTBD].[BU]),"Complete","Incomplete") AS Complete
FROM qryID_and_BU LEFT JOIN qryRecordsWithTBD ON qryID_and_BU.BU = qryRecordsWithTBD.BU;

SQL for higher level query:

SELECT SharepointIDs_to_BusinessUnits.SharepointID, SharepointIDs_to_BusinessUnits.BusinessUnitID, SharepointIDs_to_BusinessUnits.BusinessUnitName, qryID_and_BU_CompleteStatus.Complete
FROM SharepointIDs_to_BusinessUnits LEFT JOIN qryID_and_BU_CompleteStatus ON SharepointIDs_to_BusinessUnits.BusinessUnitID = qryID_and_BU_CompleteStatus.BU;

 

[The_Doc_Man]

Good question. Don't forget that for LEFT JOIN cases, two things happen, Every record on the left side of the relationship of the ON clause will appear in the output set, but you will get nulls for anything that came from the RIGHT side of the relationship if there is no matching record.

If the higher-level query includes fields that originated from the left side of a LEFT JOIN then they will be in the left side of that higher query, too. Would that perhaps explain where things crawled in?

 

[Isaac]

Good question. Don't forget that for LEFT JOIN cases, two things happen, Every record on the left side of the relationship of the ON clause will appear in the output set, but you will get nulls for anything that came from the RIGHT side of the relationship if there is no matching record.

If the higher-level query includes fields that originated from the left side of a LEFT JOIN then they will be in the left side of that higher query, too. Would that perhaps explain where things crawled in?

Hmm..Ok, so I think you are suggesting that maybe the reason I am seeing something OTHER than Nulls coming from the table on the right side of the join (even though the joined field value isn't present in that table) is because it's actually coming from the left side? Maybe I had an ambiguous reference and a similar named field?

That might be possible. I was under a terrible time crunch and I abandoned the whole thing and went a different direction. Like I said it had been quite a while since I did any hardcore querying in Access (not that this is hardcore, but I mean in the past year or two I mostly just did sql and then used Access as a FE to produce reports off of sql views), so i'm encountering weird things that seemed so easy in SSMS but I think the Access interface is just making me feel generally confused. It will pass : )