Query outer joins from same table

[rob.lyles]

Hi,
I please need help with the following. I have 2 fields from 2 different tables, Charge Branch and Source Branch, in which contain the branch number associated with charge or source. Then I have a table in which I store the branch name (Branch), and I join the number to the name. To make the joins, I display table Branch twice, and then I join the branch numbers field from Branch to the Charge and Source Branch fields. When I run the query, my branch name fields are misplaced in comparison to Design View. I.E., my branch name for Source Branch is beside Charge Branch, and my other branch name is the last field in the ran query.

I don't know if I'm completely off target in trying to do this, but any advice will be appreciated.

Thanks,
Rob

 

[pbaldy]

You'll notice that Access aliased one of the instances of the table (in may be easier to see in SQL view). You would need the appropriate table name (original or alias) for each of the branch name fields. It may help if you post the SQL of the query.

 

[rob.lyles]

Here is the SQL. Please let me know if this what you meant. Thanks.

SELECT LineitemGM.[ORDER NO], OrderGM.[Order Type], LineitemGM.Date, OrderZSA.CUST, OrderGM.[Ship To], OrderZSA.[CHRG BR], BRANCH.LOC, OrderGM.ISR, LineitemGM.[LINE NO], LineitemGM.[PART NO], LineitemGM.MT, LineItemZSA.IC, LineitemGM.[SRC BR], BRANCH_1.LOC, LineitemGM.[QTY INV], LineitemGM.[Unit Resale], LineitemGM.[Unit Cost], LineitemGM.CORR, LineItemZSA.[How Price], Pointer.Pointer, Pointer.[Pointer Name]
FROM BRANCH AS BRANCH_1 RIGHT JOIN (BRANCH RIGHT JOIN (Pointer RIGHT JOIN ((OrderZSA RIGHT JOIN (LineitemGM LEFT JOIN LineItemZSA ON (LineitemGM.[ORDER NO] = LineItemZSA.[ORDER NUMBER]) AND (LineitemGM.[LINE NO] = LineItemZSA.[ORDER LINE]) AND (LineitemGM.[PART NO] = LineItemZSA.[ITEM ID])) ON OrderZSA.[ORDER NUMBER] = LineitemGM.[ORDER NO]) LEFT JOIN OrderGM ON LineitemGM.[ORDER NO] = OrderGM.[ORDER NO]) ON Pointer.CUST = OrderZSA.CUST) ON BRANCH.BR = OrderZSA.[CHRG BR]) ON BRANCH_1.BR = LineitemGM.[SRC BR];

 

[pbaldy]

That's what I meant, though it's more complicated than I hoped. Off the top, it looks right. It appears the un-aliased copy of branch is joined on the charge field, and you have that next to the charge branch. Same for the aliased copy of branch and SRC, which I assume is source. Can you post the db?