look for a match in multiple fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LearningAccess - 17 Apr 2008 18:20 GMT

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!

Reply to this Message

KARL DEWEY - 17 Apr 2008 21:21 GMT

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 #];

Signature

KARL DEWEY
Build a little - Test a little

> I don't know if this is possible.
> I have tblA with serial #, tblB has serial #, vendor Serial #., location
[quoted text clipped - 5 lines]
> non-match queries)
> THANKS!

Reply to this Message

KARL DEWEY - 17 Apr 2008 21:22 GMT

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 #];

Signature

KARL DEWEY
Build a little - Test a little

> I don't know if this is possible.
> I have tblA with serial #, tblB has serial #, vendor Serial #., location
[quoted text clipped - 5 lines]
> non-match queries)
> THANKS!

Reply to this Message