Full outer join (from UNION ALL on 3 queries) returning same rows more than one

[Access_Novice]

I took 3 select queries and did a UNION ALL to get a FULL outer join. I see some records repeating, 3 times to be exact. I presume the first occurrence is from the INNER join, the second from the LEFT outer join and the third from the RIGHT outer join.

How can I get these repeating records to appear only once?

In the case of all other records, i.e. LEFT outer join that does not match with the "Right" table and vice versa, it seems that they are appearing only once, which is fine.



I have tried the GROUP BY statement, then listed all fields after each query. This didn't work. I tried the GROUP BY statement just once after all 3 queries and still this did not work.

[June7]

Build the UNION query then use it as source for other queries.

[Access_Novice]

Ok. I created the Union query and I'm using it as a source now. I can't do Group By because then I will be asked to specify an aggregate function which I don't want or need.

I found a Query property called "Unique Records" which I changed to "Yes." But this did not remove duplicate records.

Any ideas?

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Access_Novice]

If you want to provide db for analysis, follow instructions at bottom of my post.

I tried using SELECT DISTINCT in the SQL code and it worked.