Memo field is automatically changing to text field in count query

[robert]

I have a query that is using a cout option and it is automatically
transferring my memo field to a text field limiting it to 255
characters. When the query is not in 'count mode' the memo field is
correct, how can i fix this? It seems to do this whenever i click the
sigma function. I played around and get an error that you cannot have
a MEMO field with an aggregate function, what is the difference, why
is this, is there a work around? here is the sql:

SELECT [abstracts with authors].Title, Count([abstracts with
authors].Title) AS CountOfTitle, [abstracts with
authors].contactEMAIL, [abstracts with authors].Status, [abstracts
with authors].FirstName, [abstracts with authors].LastName, [abstracts
with authors].Type, "Dear " & [FirstName] & ";" AS emailMESSAGE1,
QRY_accept_reject.emailMESSAGE2
FROM QRY_accept_reject INNER JOIN [abstracts with authors] ON
QRY_accept_reject.statusTYPE = [abstracts with authors].statusTYPE
GROUP BY [abstracts with authors].Title, [abstracts with
authors].contactEMAIL, [abstracts with authors].Status, [abstracts
with authors].FirstName, [abstracts with authors].LastName, [abstracts
with authors].Type, "Dear " & [FirstName] & ";",
QRY_accept_reject.emailMESSAGE2
HAVING ((([abstracts with authors].Status)<>"Unsure"));

 

[robert]

I think I'm an idiot, i think i may have figured it out, i created a
query without the join and called it 'part 1' and created a 'part 2'
query and joined it w/ the memo field, i think for now that is the
work around, but if there is a better way please lemme know, i'm using
access 2003

 

[Maurice]

memo is not an option in a union query...

 

[John W. Vinson]

I think I'm an idiot, i think i may have figured it out, i created a
query without the join and called it 'part 1' and created a 'part 2'
query and joined it w/ the memo field, i think for now that is the
work around, but if there is a better way please lemme know, i'm using
access 2003

The original problem is that when you GROUP BY a memo field, Access must
exclude duplicates... and it truncates the memo field to 255 bytes so it
doesn't have to compare (up to) a gigabyte of data in each of (up to) millions
or records. If you can count on the memo being unique given other grouping
fields, don't group by it, but instead use the FIRST aggregate function to
just pick one instance of the memo.