psonix
asked on
Memo Field being truncated by Group By
I am sure that there is a better way to write this query, but I don't know what it is.
SELECT qry_SearchUnion.Problem, Count(qry_SearchUnion.Reca pID) AS CountOfRecapID, qry_SearchUnion.Job_Name, qry_SearchUnion.Date, qry_SearchUnion.Error_Code , qry_SearchUnion.Solution, qry_SearchUnion.Primary, qry_SearchUnion.Secondary, qry_SearchUnion.External_P eople, qry_SearchUnion.[Type of System], qry_SearchUnion.Peregrin_T icket, qry_SearchUnion.ID, qry_SearchUnion.[Open Time], qry_SearchUnion.[Update Time], qry_SearchUnion.[Alert Status], qry_SearchUnion.Severity, qry_SearchUnion.[CI Severity], qry_SearchUnion.Category, qry_SearchUnion.Title
FROM qry_SearchUnion
GROUP BY qry_SearchUnion.Problem, qry_SearchUnion.Job_Name, qry_SearchUnion.Date, qry_SearchUnion.Error_Code , qry_SearchUnion.Solution, qry_SearchUnion.Primary, qry_SearchUnion.Secondary, qry_SearchUnion.External_P eople, qry_SearchUnion.[Type of System], qry_SearchUnion.Peregrin_T icket, qry_SearchUnion.ID, qry_SearchUnion.[Open Time], qry_SearchUnion.[Update Time], qry_SearchUnion.[Alert Status], qry_SearchUnion.Severity, qry_SearchUnion.[CI Severity], qry_SearchUnion.Category, qry_SearchUnion.Title
HAVING (((Count(qry_SearchUnion.R ecapID))=8 ))
ORDER BY Count(qry_SearchUnion.Reca pID) DESC;
The memo fields qry_SearchUnion.Problem and qry_SearchUnion.Solution get truncated to 255 characters because of the group by. To test this I copied the query, removed the aggregates and the memos worked on a form. I need to have the Count(qry_SearchUnion.Reca pID) AS CountOfRecapID in there, but the other group bys are there just so it wont throw the aggregates error.
I saw this touched on : https://www.experts-exchange.com/questions/20948020/MS-Access-2000-query-memo-field-truncated-to-255-characters-because-of-GROUP-BY-clause.html?query=query+truncates+memo+field&clearTAFilter=true
but I couldn't get it to work for me, any advice?
I am using Access 2003.
SELECT qry_SearchUnion.Problem, Count(qry_SearchUnion.Reca
FROM qry_SearchUnion
GROUP BY qry_SearchUnion.Problem, qry_SearchUnion.Job_Name, qry_SearchUnion.Date, qry_SearchUnion.Error_Code
HAVING (((Count(qry_SearchUnion.R
ORDER BY Count(qry_SearchUnion.Reca
The memo fields qry_SearchUnion.Problem and qry_SearchUnion.Solution get truncated to 255 characters because of the group by. To test this I copied the query, removed the aggregates and the memos worked on a form. I need to have the Count(qry_SearchUnion.Reca
I saw this touched on : https://www.experts-exchange.com/questions/20948020/MS-Access-2000-query-memo-field-truncated-to-255-characters-because-of-GROUP-BY-clause.html?query=query+truncates+memo+field&clearTAFilter=true
but I couldn't get it to work for me, any advice?
I am using Access 2003.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried the second link that creates the temp table in the subquery, and that will get the memo fields through, but I can't find a way to get the count function involved
have you tried expanding your OrderBy clause to?
ORDER BY Count(qry_SearchUnion.Reca pID) DESC, qry_SearchUnion.Problem, qry_SearchUnion.Solution;
ORDER BY Count(qry_SearchUnion.Reca
ASKER
I ended up using the second link and having to use it as a base to create a work around off of.
OK glad you're winning.
ASKER