Work around for Full Outer Join

[toon10]

Hi

I have two queries which return data. I then have another query which joins both of these queries together. So, I get all trade data and all commission data and join the two. The problem is I need a full outer join so I catch all records, some have just trade, some have just commssion and some have both. Is there a way to get around Access not allowing a full outer join??

Thanks

 

[Frink]

Hallo,

Can't you use a UNION query for the joining one?

- Frink

 

[toon10]

My query works with a left join in that it returns all trade data but it only returns commssion data where there is a match. I can't use a union in the query builder in "design mode" but if I switch to SQl mode, I get errors on my GroupBy statements.

Thanks
Andrew

 

[Frink]

Hallo,

Sometimes it's easier to produce two queries which return the data you want and then use:
SELECT * FROM qryTradeData UNION SELECT * FROM qryCommissionData;
If you want then sorted and it doesn't seem to behave as you expect, then create a 4th query SELECT * FROM qryTradeAndCommissionData ...

- Frink