Distinct Query with Memo Fields

[lolab]

I have looked through old posts on this and haven't found an answer, any suggestions would be great - Thanks.

Notes:
ACESS 2007
distinct_ids = 390 records
memodata.id = 714 records
RIGHT JOIN returns 714 records (with SELECT DISTINCT, memo fields are trunctated)

SELECT ids_memodata.*
FROM ids_memodata
RIGHT JOIN distinct_ids
ON ids_memodata.id = distinct_ids.id;
 

[Stewart Ross]

Unfortunately, Access truncates memo fields to the same length as text fields (255 characters) when grouping is involved, for practical reasons associated with how grouping is implemented. This really rules out the use of Group By or Select Distinct where memo fields are involved - with one exception for Group By queries.

Depending on the content of the memo field in relation to the other grouped fields, you may be able to use a totals (Group By) query instead of your Select Distinct one. If you substitute the memo value with a calculated field using one of the aggregate functions such as First, Last, Max or Min as a 'dummy function' on the memo field the memo field value will be returned without truncation.

The down side with the use of aggregates as 'dummy functions' to retain the memo field values is that if you do indeed require grouping on the memo contents then First, Last etc will not be suitable, as they will return specific values consistent with their purpose, not the underlying grouping you may require.

Whatever you do, don't apply SELECT DISTINCT to the results of such a query, or use it in another query with a GROUP BY clause on the calculated field - the memo field will once again be truncated back to 255 characters if you do!

-Stewart

[lolab]

Thank you for the response! Although, I don't fully understand how to use the group by and aggregate functions. I need to look into this option further. However, when testing it I created a backup copy of the database and in doing so the JOIN query (above) worked!!! Access seems to do funny things sometimes I guess. Hope this helps others in the same boat.