SQL with Right & Left join help needed

[foxhound]

Hello,

I have two tables that I have combined in a union query as such:

SELECT [3DN], [Grp], [LOC], [BU], [TYPE], [XREF], [Sum Of ACTNUM] AS [ACTNUM], [ATHNUM]
FROM [qry_Actual Positions]

UNION SELECT [3DN], [Grp], [LOC], [BU], [TYPE], [XREF], [ACTNUM], [Sum Of ATHNUM] AS [ATHNUM]
FROM [qry_Authorized Positions];

[XREF] is my linked field. My question is how can I modify the above code to have [ACTNUM] and [ATHNUM] appear on the same row as one record instead of two separate records. Also, there are some records in [qry_Actual Positions] that aren't in [qry_Authorized Positions] and vice versa. I would like all records from both queries to be included.

Any assistance would be greatly appreciated.

Thanks in advance,

Foxhound

 

[SydneyGeek]

Access spits the dummy if you try more than one outer join in a single query -- you'll get "Ambiguous Outer Join" errors. I get around this by stringing queries together in a sequence, each of which has a single outer join.

HTH
Denis

 

[foxhound]

Denis,

Thanks for the suggestion!