In Access, how can I accomplish a conditional or multiple join?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I accomplish a conditional or multiple join?

I have two tables:

Payees:
OldID NewID PayeeName
10001 10001 Alan
20002 20001 Bob
30003 30001 Carl
40004 40004 Doug

Members:
Member MembAcct
10001 Xxx
20002 Yyy
30001 Zzz
40001 Vvv

I want these results:

OldID NewID PayeeName MembID MembAcct
10001 10001 Alan 10001 Xxx
20002 20001 Bob 20002 Yyy
30003 30001 Carl 30001 Zzz

As you can see:
- OldID can be same or different than NewID.
- Member can match OldID or NewID, if different, or neither.

If I'm going at this with the wrong approach... please, any ideas will be
welcome.

Thanks!
SharonFinLV
 
Hi,



SELECT a.*, b.*
FROM Payees INNER JOIN Members
ON Members.Member IN( Payees.OldID, Payees.NewID)



Note that the ON clause can be any expression, not just a comparison.

Hoping it may help,
Vanderghast, Access MVP
 
Back
Top