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