Conditional Join

[hlock]

Windows 7, Access 2007: I have really been struggling with this problem. I understand what I need to do, I just don't know how to do it. In joining two tables together:

TableA
OrigNum

TableB
NameNum
OrigNum
PrevNum
NextNum

I want to join TableA.OrigNum to TableB.OrigNum where TableB.NextNum is null

else

join TableA.OrigNum to TableB.NameNum where TableA.OrigNum <> TableB.OrigNum and TableA.OrigNum = TableB.NameNum and TableB.NextNum is null.

Thanks in advance for any help.

 

[plog]

join TableA.OrigNum to TableB.OrigNum where TableB.NextNum is null

else

join TableA.OrigNum to TableB.NameNum where TableA.OrigNum <> TableB.OrigNum and TableA.OrigNum = TableB.NameNum and TableB.NextNum is null

You need to parse that a little better because the second portion (everything after the 'else') will never meet its criteria. The last condtion of the second portion (' and TableB.NextNum is null') will never be true because if TableB.NextNum is null, it will be dealt with in the first portion.

Add some parenthesis, think about it a little more and try and state it as simply as you can.

 

[hlock]

Thanks. With the two tables

TableA
OrigNum

TableB
NameNum
OrigNum
PrevNum
NextNum

I want to retrieve the corresponding records from TableB when TableA.OrigNum=TableB.OrigNum and TableB.NextNum is null.

Otherwise, I want to retrieve the corresponding records from TableB when TableA.OrigNum<>TableB.OrigNum and TableA.OrigNum=TableB.NameNum.

I hope this is a little clearer.

 

[plog]

A little. What I would do is create a sub-query based on TableB and LEFT JOIN TableA, then I would create a calculated field to determine what the value is you want to link on. This would be that query:

SELECT TableB.OrigNum, TableB.NameNum, TableB.NextNum, IIf(IsNull([NextNum]),[TableB].[OrigNum],IIf([TableB].[OrigNum]<>[TableA].[OrigNum],[NameNum])) AS BLinkNum
FROM TableA RIGHT JOIN TableB ON TableA.OrigNum = TableB.NameNum;

It will display all the records in B and then calculate a new field called 'BLinkNum' which you can use in another query to link to a based on the rules you have set out.

If it doesn't work like you want, let me know and also include some sample data from TableA and TableB for which it fails.