Access Query Memo field truncation due to "distinct"

Question

I am having problems running a query without either truncating the note field in NotesTbl or returning repeated entries.

UID is not unique for AccessTbl. When I leave out "distinct" notes will return multiple times because I am joining with AccessTbl on a non-distinct condition. When I use distict, the note field is trunctated because it is a memo field.

Here is my query:

SELECT DISTINCT NotesTbl.pin, NotesTbl.noteid, NotesTbl.note, NotesTbl.date,
AccessTbl.affiliation, AccessTbl.name
FROM NotesTbl
LEFT JOIN AccessTbl
ON NotesTbl.UID = AccessTbl.UID
WHERE PIN = #pin#
AND UID = '#uid#'
ORDER BY NotesTbl.DATE DESC

Answer 1

Edit: --Removed first suggestion--

... Another method would be to break your request into two queries: One that refines AccessTbl so that UID is unique within the query, and another that joins NotesTbl to the qryAccessTblUnique query you just created.

Answer 2

The Access database engine normally determines uniqueness of text ('String') data using only the first 255 characters and that is why DISTINCT and GROUP BY will truncate.

This suggestion is a bit long winded but it does work: split the MEMO into chunks of 255 characters, do use DISTINCT on the chunks, then concatenate them back together again e.g. (Access database engine ANSI-92 Query Mode syntax i.e. parens for subqueries):

SELECT DT2.MyMemoCol_1 & DT2.MyMemoCol_2 AS MyMemoCol
  FROM (
        SELECT DISTINCT DT1.MyMemoCol_1, DT1.MyMemoCol_2
        FROM (
              SELECT MID(MyMemoCol, 1, 255) AS MyMemoCol_1, 
                     MID(MyMemoCol, 256, 255) AS MyMemoCol_2 
                FROM Test1
             ) AS DT1
       ) AS DT2;

---

A comment has been posted:

Breaking the memo down in to 255-character chunks is entirely unnecessary. You can simply sort on Left(MyMemoCol, 8192) or some other appropriately chosen value for the field length returned.

Well, in my testing this doesn't work at all. Quick repro:

CREATE TABLE Test1 (MyMemoCol MEMO NOT NULL);

INSERT INTO Test1 (MyMemoCol) VALUES (STRING(300, 'A'));
INSERT INTO Test1 (MyMemoCol) VALUES (STRING(300, 'A') & STRING(5, 'X'));
INSERT INTO Test1 (MyMemoCol) VALUES (STRING(300, 'A'));

SELECT LEFT$(MyMemoCol, 8192)
  FROM Test1
 GROUP 
    BY LEFT$(MyMemoCol, 8192);

Tested using the SQL view of a Access2007 .accdb ACE engine Query object in SQL-92 Query Mode, the query returns a single row (incorrect) whose value has been truncated at 255 characters (incorrect).

The earlier 'chunking' query returns two rows (correct) without truncation (correct).

Answer 3

You could also filter the query object on the CF end to remove (or ignore) responses which have already been displayed. Not efficient, but if you are using Acess, I'm guessing heavy traffic is not a huge issue.

Something like:

<cfoutput query="notes">
   <cfset diplay="true">
   <cfloop from="1" to="#notes.currentrow-1#">
       <cfif note neq notes.note[i]>
            <cfset display="false">
       </cfif>
    </cfloop>
    <cfif display>
       #note#
    </cfif>
</cfoutput>

For large numbers of returns n, this is going to be ugly at O(n^2), but for small n, it should give you what you want.

Answer 4

I found a solution that seems to work. I used a "group by" to force distinctness on the PIN and NoteID. I tried to exclude the note from distinctness comparissons by using First() to avoid truncation.

SELECT NotesTbl.pin, NotesTbl.noteid, First(NotesTbl.note) as notebody, NotesTbl.date,
AccessTbl.affiliation, AccessTbl.name
FROM NotesTbl
LEFT JOIN AccessTbl
ON NotesTbl.UID = AccessTbl.UID
WHERE PIN = #pin#
AND UID = '#uid#'
GROUP BY pin,affiliation,name,date,noteid
ORDER BY NotesTbl.DATE DESC