look for a match in multiple fields

[LearningAccess]

I don't know if this is possible.
I have tblA with serial #, tblB has serial #, vendor Serial #., location
tblA.Serial# could be in tblB.serial# or tblB.[vendor Serial#] ..is there a
way to say
something like .. iif(tblA.Serial#]<>tblB.[tblB.serial#then look in
tblB.[vendor Serial#], then if it's a match, then return location???
just being lazy about creating possibly 4 queries (2 match queries and 2
non-match queries)
THANKS!

 

[KARL DEWEY]

This is close I think --
SELECT tblA.[Serial #], IIf([tblB].[Serial #] Is
Null,[tblB_1].[location],[tblB].[location]) AS Expr1
FROM (tblA LEFT JOIN tblB ON tblA.[Serial #] = tblB.[Serial #]) LEFT JOIN
tblB AS tblB_1 ON tblA.[Serial #] = tblB_1.[Serial #];

 

[KARL DEWEY]

Corrected join.

SELECT tblA.[Serial #], IIf([tblB].[Serial #] Is
Null,[tblB_1].[location],[tblB].[location]) AS Expr1
FROM (tblA LEFT JOIN tblB ON tblA.[Serial #] = tblB.[Serial #]) LEFT JOIN
tblB AS tblB_1 ON tblA.[Serial #] = tblB_1.[Vendor Serial #];