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