[AccessD] Select Distinct not working on union query - due to MEMO field?

William Benson (VBACreations.Com) vbacreations at gmail.com
Mon Jul 11 16:10:46 CDT 2011


I was using Select distinct on records from a subquery which itself was the
result of a union query. To my mind, Select distinct should produce the same
result in such a situation as a group by query using the same fields. The
Group By query condensed the records, however, and the select distinct query
did not. I chased down the reason to this:  One of the fields was a memo
field.

I don't know if the takeaway here is

(1) Use Group By queries instead of Select Distinct, whenever possible
(2) Don't use MEMO fields whenever possible
(3) I have not really discerned the real reason for the difference and am
fooling myself.

' METHOD 1 - SURPRISE, TOO MANY RECORDS
Select Distinct Field1, Field2, ... FieldN
From
(
	Select
	     Field1, Field2, ... FieldN 
	From Tbl1
     Union All
	Select
    	     Field1, Field2, ... FieldN 
	From Tbl2
)



'METHOD 2 - You can trust this result

Select Field1, Field2, ... FieldN
From
(
	Select
	     Field1, Field2, ... FieldN
	From Tbl1
     Union All
	Select
	     Field1, Field2, ... FieldN 
	From Tbl2
)
Group By
	Field1, Field2, ... FieldN




More information about the AccessD mailing list