Help with Expression within Query

[hafner]

Hello,

I'm building a query in which a master set of data is linked to a
secondary table with the use of a unique identifier.

Basically, I'm looking to represent in the query whether or not the
line of data in the master table appears in the secondary table. I can
do that easily by showing all records in the master table and only
those lines in the secondary table that match.

However, what I'd like to do is create an expression that will say
"Yes" if the secondary table does not match that record and "No" if the
secondary table does match that record.

I built this expression:
Say Yes If Doesn't Match: IIf([SecondaryTable].[Unique
Identifier]=Null,"Yes","No")

However, it says "No" for every record, whether or not it matches to a
recordthe secondary table.

Can anybody point me in the right direction on this? Many thanks in
advance for the help.

Cheers,
Chris Hafner

[pietlinden]

what if you just use a left join, and then test the foreign key of the
right side table for null?

SELECT A.Field1, A.Field2, B.ForeignKey, B.Field3...,
iif(IsNull(B.ForeignKey), "Yes","No") AS FKFieldIsNull
FROM A LEFT JOIN B ON A.PrimaryKey=B.ForeignKey)
....

You can get most of this done by using the "Find Unmatched" query
wizard and then adding a field to your query and testing the foreign
key of the "right side" table (the one that may not have records
corresponding to the other table's PK) that returns Yes or No.

(Yeah, I bet that explanation totally confused you... well, try the SQL
and go from there...)

Hope it helps a little.