Solved

Memo Field being truncated by Group By

Posted on 2006-06-13
6
192 Views
Last Modified: 2006-11-18
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.RecapID) 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_People, qry_SearchUnion.[Type of System], qry_SearchUnion.Peregrin_Ticket, 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_People, qry_SearchUnion.[Type of System], qry_SearchUnion.Peregrin_Ticket, 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.RecapID))=8))
ORDER BY Count(qry_SearchUnion.RecapID) 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.RecapID) AS CountOfRecapID in there, but the other group bys are there just so it wont throw the aggregates error.

I saw this touched on : http://www.experts-exchange.com/Databases/MS_Access/Q_20948020.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.
0
Comment
Question by:psonix
  • 3
  • 3
6 Comments
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 16895995
0
 

Author Comment

by:psonix
ID: 16897168
tried both of those and they don't work.
0
 

Author Comment

by:psonix
ID: 16897742
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16898748
have you tried expanding your OrderBy clause to?
ORDER BY Count(qry_SearchUnion.RecapID) DESC, qry_SearchUnion.Problem, qry_SearchUnion.Solution;
0
 

Author Comment

by:psonix
ID: 16904271
I ended up using the second link and having to use it as a base to create a work around off of.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16904471
OK glad you're winning.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now