union query and eliminate duplicates

[Jake]

I have a union query:
SELECT *
FROM tblRequiredRecordsDND
UNION ALL
SELECT *
FROM tblRequiredRecords;

I used the ALL statement as some of the fields in the tables have memo, OLE,
and hyperlink fields. The ALL statement duplicates records. I want to find
all the records between the two tables that are not duplicated. Is this
possible?
Thanks!
Jake

 

[John Spencer (MVP)]

I can't think of any way to do it with Memo and OLE fields. You would have to
use the Union query as the source of an aggregate (Totals) query and specify the
field names.

SELECT FieldA, FieldB, FieldC, First(MemoField) as fMemoField, First(OleField)
as fOleField, First(hyperlinkField)
FROM TheUnionQuery
GROUP BY FieldA, FieldB, FieldC

This will (more or less) randomly select one of the MemoField, OleField, and
Hyperlink field that matches with the Group By clause. If that isn't
satisfactory, then I don't know of a way for Access to give you the results you want.