Re: Using GROUP BY in query expression on MEMO fields it truncates data in MEMO fields to first 255 characters
From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: Tue, 7 Dec 2004 11:53:48 -0600
Access is telling you that you can't have more than one "Value". You can
have many "First" as Row Headings.
-- Duane Hookom MS Access MVP "[SolarAngel]" <not-for-mail> wrote in message news:uDc46mH3EHA.firstname.lastname@example.org... > I would do something like that, but unfortunately I already have TRANSFORM and PIVOT with First() > (Access tell me that I can't use more than one First for Value), > I have INNER JOINS, LEFT JOIN, ... 7 tables linked with one that is rotated (simple query) q=). > > yes, custom build (VB immediate print out): > > TRANSFORM First(Company_Custom_Field_Data.Value) AS FirstOfValue SELECT Company.Code_Company, Company_Address.Code_Company_Address, > Company_Address.Company_Name AS Address_Company_Name, Company.Company_Name, Company.Complement_Name, > Company_Address_Type.Company_Address_Type_Name, Company_Address.Address_1, Company_Address.Address_2, Company_Address.Address_3, > Company_Address.Zip_Code, Company_Address.Town, Company_Address.Country, Company_Address.Tel, Company_Address.Fax, > Company_Status.Company_Status_Name AS Company_Status, Company.Site_Web, Company.Comments FROM Company_Status RIGHT JOIN > (Company_Address_Type RIGHT JOIN ((Company LEFT JOIN (Tables_Reference RIGHT JOIN Company_Custom_Field_Data ON > Tables_Reference.Code_table = Company_Custom_Field_Data.Code_Table) ON Company.Code_company = > Company_Custom_Field_Data.Code_Company) LEFT JOIN Company_Address ON Company.Code_company = Company_Address.Code_company) ON > Company_Address_Type.Code_Company_Address_Type = Company_Address.Code_Company_Address_Type) ON Company_Status.Code_Company_Status = > Company.Company_Status WHERE Company.Code_Company = 3550 GROUP BY Company.Code_Company, Company_Address.Code_Company_Address, > Company_Address.Company_Name , Company.Company_Name, Company.Complement_Name, Company_Address_Type.Company_Address_Type_Name, > Company_Address.Address_1, Company_Address.Address_2, Company_Address.Address_3, Company_Address.Zip_Code, Company_Address.Town, > Company_Address.Country, Company_Address.Tel, Company_Address.Fax, Company_Status.Company_Status_Name , Company.Site_Web, > Company.Comments PIVOT Tables_Reference.Code_table > > -OR OUTPUT FOR MORE COMLEX BUILD FOR OFFERS- > > TRANSFORM First(Offer_Custom_Field_Data.Value) AS FirstOfValue SELECT Offer.Code_Offer, Offer.Code_Interlocutor, > Offer.Code_Product_Type, Offer.Date_Offer, Trim(Interlocutor.Civility + ' ' + Interlocutor.First_Name + ' ' + > Interlocutor.Last_Name) AS Interlocutor, Users.Full_Name AS Responsible, Offer.Date_Ratification, Offer.Probability, > Offer_Status.Offer_Status_Name, Offer.Comment, Offer.Offer_Price, Offer.Offer_Price_WTax, Products.Product_Name, > Offer_Products.Price, Offer_Products.Quantity, ((Offer_Products.Tax * 0.01) * Offer_Products.Price) AS Price_WTax, > Offer_Products.Delivered, Offer_Products.Delivered_Date, Offer_Products.Comments FROM Interlocutor RIGHT JOIN (Products RIGHT JOIN > (Users RIGHT JOIN (Offer_Status RIGHT JOIN (Offer_Products LEFT JOIN (Tables_Reference RIGHT JOIN (Offer_Custom_Field_Data RIGHT > JOIN Offer ON Offer_Custom_Field_Data.Code_Offer = Offer.Code_Offer) ON Tables_Reference.Code_table = > Offer_Custom_Field_Data.Code_Table) ON Offer_Products.Code_Offer = Offer.Code_Offer) ON Offer_Status.Code_Offer_Status = > Offer.Code_Offer_Status) ON Users.Code_User = Offer.Responsible) ON Products.Code_Product = Offer_Products.Code_Product) ON > Interlocutor.Code_interlocutor = Offer.Code_Interlocutor WHERE Offer.Code_Company = 3550 GROUP BY Offer.Code_Offer, > Offer.Code_Interlocutor, Offer.Code_Product_Type, Offer.Date_Offer, Trim(Interlocutor.Civility + ' ' + Interlocutor.First_Name + ' ' > + Interlocutor.Last_Name) , Users.Full_Name , Offer.Date_Ratification, Offer.Probability, Offer_Status.Offer_Status_Name, > Offer.Comment, Offer.Offer_Price, Offer.Offer_Price_WTax, Products.Product_Name, Offer_Products.Price, Offer_Products.Quantity, > ((Offer_Products.Tax * 0.01) * Offer_Products.Price), Offer_Products.Delivered, Offer_Products.Delivered_Date, > Offer_Products.Comments PIVOT Tables_Reference.Code_Table > > And field where I have this problem is in first Company.Comments, and second sample Offer.Comment, Offer_Product.Comments, ...(more > in other custom build queries) > > as I have figured out you are telling me to set First(Offer.Comments) AS Comments will return me full value of MEMO field? > > u jeeee it works q=0, only now I have to detect which field is it, ... > I must see how this looks like in Design. > ... this can not be applied in Design you must manually change SQL, this probably why I couldn't make it right in Design, > I was experimenting by Access always returns me error, you must this, you must that, you may not, ... > > Thanks man, you have saved me a 4 hours at least > > q=) > > [SolarAngel] > > > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message news:OUZxX0G3EHA.1144@TK2MSFTNGP09.phx.gbl... > | As Doug explained, this behavior is by design. > | > | If Access did really have to perform string comparison operations based on > | thousands or even millions of characters, the queries would be completely > | unworkable. > | > | Work around the issue by not asking Access to do such as thing. For example, > | if you want the full memo in a GROUP BY query, then use First() on the memo > | field so that Access can just return the whole thing instead of needing to > | group by the field. > | > | -- > | Allen Browne - Microsoft MVP. Perth, Western Australia. > | Tips for Access users - http://allenbrowne.com/tips.html > | Reply to group, rather than allenbrowne at mvps dot org. > | > | "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message > | news:uM%230f%23E3EHA.1292@TK2MSFTNGP10.phx.gbl... > | > That's the way it has to work, unfortunately. > | > > | > Whenever you need to sort on a Memo field, truncation takes place. That > | > means using a Memo field in conjunction with ORDER BY, UNION (as opposed > | > to > | > UNION ALL) or GROUP BY will cause truncation. > | > > | > -- > | > Doug Steele, Microsoft Access MVP > | > http://I.Am/DougSteele > | > (No private e-mails, please) > | > > | > > | > "[SolarAngel]" <not-for-mail> wrote in message > | > news:upsyQlE3EHA.1404@TK2MSFTNGP11.phx.gbl... > | >> BUG: Using GROUP BY in query expression on MEMO fields it truncates data > | > in MEMO fields to first 255 characters > | >> > | >> > | >> I have tested this in new database with 1 key column, and 2 memo fields > | > one containing less than 255 chars, and 1 with more than 255 > | >> chars in it. > | >> Created two queries one not using GROUP BY and one using it (I called > | >> this > | > one ...ERROR...) > | >> > | >> I have attached this database as example of bug. > | >> > | >> Info: bug discovered this using ADO not sure what ver. (MDAC 2.7) > | >> also Access Ver. 2000 [9.0.3821 SR-1] > | >> > | >> who needs to know, also I will test this bug on Access in Office XP > | >> latter > | > this day. > | >> > | >> Is this a Bug or a limitation I am not sure, > | >> there is no comment on this in MSDN so presume that this is a bug. > | >> > | >> what so ever it makes my life more difficult, like already it isn't q=). > | >> > | >> [SolarAngel] > | > | > >