Memo field truncating in Union ALL query 1

[MorningSun]

Many, Many, Many thanks to other posts in the forum that got me far enough to determine that this SQL WILL pull the entire memo field. Previously it was truncating at 255 characters:

SELECT tblSATEN.AuditNo, Avg(tblSATEN.Score) AS AvgOfScore, Count(tblSATEN.AuditNo) AS Transactions, "Enrollments" AS Item, First(tblSATEN.Policy) AS FirstOfPolicy, First(tblSATEN.SSN) AS FirstOfSSN, First(tblSATEN.Notes) AS FirstOfNotes, tblSATEN.Key
FROM tblSATEN
WHERE (((tblSATEN.Score) Is Not Null))
GROUP BY tblSATEN.AuditNo, tblSATEN.Key

HOWEVER, if I try to use this SQL in a UNION ALL query it truncates it again... so, the above works with no truncation but the below SQL results in a truncated memo field again:

SELECT tblSATEN.AuditNo, Avg(tblSATEN.Score) AS AvgOfScore, Count(tblSATEN.AuditNo) AS Transactions, "Enrollments" AS Item, First(tblSATEN.Policy) AS FirstOfPolicy, First(tblSATEN.SSN) AS FirstOfSSN, First(tblSATEN.Notes) AS FirstOfNotes, tblSATEN.Key
FROM tblSATEN
WHERE (((tblSATEN.Score) Is Not Null))
GROUP BY tblSATEN.AuditNo, tblSATEN.Key

UNION ALL SELECT tblSATEOI.AuditNo, avg(tblSATEOI.Score), Count(tblSATEOI.AuditNo) AS Transactions, "EOI" as Item,first(tblSATEOI.Policy),first(tblSATEOI.SSN),first(tblSATEOI.Notes),tblSATEOI.KEY
FROM tblSATEOI
Where tblSATEOI.Score is not null
GROUP BY tblSATEOI.AuditNo,tblSATEOI.Key;

Why is that? Oh, and the memo field is "Notes"

Any advice would be greatly appreciated!



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[MajP]

try replacing Union with Union All

http://allenbrowne.com/ser-63.html

 

[MajP]

Sorry, misread your query and you have a union all.

 

[MorningSun]

Thanks MajP but I have Union All in the query already.

After looking at several other posts, I am currently creating new queries for the notes using only the AuditNo, Key, and Note fields (none of which are aggregates). Therefore there is no group by statement. This method retrieves the entire Memo/note field without truncation. I still have to combine it back to the rest of the data for the report though so hopefully I will not see the truncation return on that step. If I do, I plan to use the Notes queries to put a subreport on the main report. I think that should solve the problem either way.



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.