Memo field truncated by Group By

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Memo field truncated by Group By

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-04-11 : 01:06:06

I am selecting some data from a table, and getting a count of the number of child records in the corresponding table, but one of the fields, which is a memo type, is getting truncated to about 200 characters. Here is my query:

SELECT TOP 7 pl.id, pl.title, pl.description, pl.entryDate, COUNT(p.id) AS noPhotos
FROM photoLog pl
INNER JOIN photos p ON p.logID = pl.id
WHERE pl.visible = true
GROUP BY pl.id, pl.title, pl.description, pl.entryDate
ORDER BY pl.entryDate DESC

pl.description is the field that is getting chopped off. Any ideas why?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-11 : 02:35:06

You wouldn't by any chance be calling this from an ASP page would you ?

In ASP (actually ADO) you need to have text (in SQL Server) or memo (in Access) fields last in your select list. Otherwise you get weirdness ranging from truncation to missing data.

Damian

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-04-11 : 02:39:31

I am calling it from a .Net assembly, which is called by an ASP.NET page, but even when I run the query in Access Query I still get the description truncated.

I tried putting the description at the end of the select list and still got the same result.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-11 : 13:40:54

Not sure if this is anything to do with it but -
I know SQL doesn't allow grouping on text fields (it's version of memo) - maybe MS Jet is doing something screwy behind the scenes to allow it. Are all the memo fields truncating to a consistent length, or are they all over the place?

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-04-11 : 19:23:42

pl.description is the one memo field, and it's all over the place, but always hanging around 200 chararcters

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-12 : 03:45:47

I just tried it on one of the Access DBs I support. It seems to be truncating at 255 characters, which is the maximum size for Jet text columns. Looks like the Jet engine automatically converts a Memo column to a Text(255) column before it runs the grouping.
What you might have to do in your case is to create an intermediate query that does the counting:

qryPhotoCount (new query):

SELECT logid, COUNT(id) as noPhotos
FROM photos

finished query:

SELECT TOP 7 pl.id, pl.title, pl.description, pl.entryDate, pc.noPhotos
FROM photoLog pl INNER JOIN qryPhotoCount pc ON pc.logID = pl.id
WHERE pl.visible = true
ORDER BY pl.entryDate DESC

This eliminates the need for grouping on the memo columns, so you won't lose any information

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-04-14 : 19:35:00

Thanks Timmy, that worked, I just had to put the GROUP BY logid in the qryPhotoCount query and it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources