SELECT vs. SELECT DISTINCT

[Genalube]

I am running a query that includes the inclusion of memo fields, my query pulls from four different tables with one to many relationships between them. The relationships are such that when I run my query I get several identical records.

I thought I could just put the DISTINCT keyword in and solve this problem. It does but the 255 character limitation that crops up with memo fields, rears its head and cuts out large chunks of the memo field. (see http://www.thescripts.com/forum/thread190112.html)

Parts of the two versions of the query are here (the second is the memo field:

SELECT Records.EntryNo , Records.RecDesc

SELECT DISTINCT Records.EntryNo , Records.RecDesc

Is there a way to cut out the duplicates (based on the EntryNo field values) that won't make the memo field data get all funky on me.

[nico5038]

Hmm, I'm afraid this won't be possible by queries due to the length limitation you found.
I would probably use a sorted recordset and code the comparison between the Memo fields in the loop by saving the "first" memofield and when equal to the "next" memofield then no new record is written.

Getting the idea ?

Nic;o)

[schillo]

Do you know whether this limitation has been solved in Access 2007?


Kind regards