BUG: Group by memo field in A2k or AXP
Peter Miller
Back in December 2000, a hsande...@my-deja.com reported a problem
with grouping by memo fields in Access 2000. Nobody ever followed up
(according to google).
Would any microsoft folk like to comment on the bug in Access 2000 and
Access XP that makes grouping by memo fields in multi-table queries
unreliable?
I'm not talking about the 255 character limit. That's a published
limitation and not a bug. I'm talking about something as simple as
the following:
TableOne:
FieldA: long integer
FieldB: memo
Records (3) 1, "one"
2, "two"
3, "three"
TableTwo:
FieldC: Long integer
Records (3) 1
2
3
QueryOne:
select fielda, fieldb
from tableone as t1 inner join tabletwo as t2 on t1.fielda = t2.fieldc
group by fielda, fieldb;
The query works fin if only tableone is referenced, but including the
second table, whether or not any fields from the second table are
used, causes a complete breakdown.
The kb has nothing on this (no, q88926 is not relevant here because it
refers to Access 97 and prior, when you couldn't group by a memo
field).
Anyone care to comment on this?
Peter Miller
PK Solutions
_____________________________________________________
For Microsoft Access related tools and services,
including our Data Recovery Rescue Service for
Microsoft Access, please visit our site (below)...
_____________________________________________________
www: www.pksolutions.com
e-mail: pmi...@pksolutions.com
Tel: +1 (800) 987-7716 Fax: +1 (619) 839-3900
_____________________________________________________
Duane Hookom
any version of Access. I can't imagine ever using a memo field in a way that
would require that I either sort or group by it. I'm sure that your query
would work with the following change
select fielda, Left(fieldb , 255) as B
from tableone as t1 inner join tabletwo as t2 on t1.fielda = t2.fieldc
SQL Server doesn't allow you to group by a TEXT field (similar to Access
MEMO fields)
--
Duane Hookom
Microsoft Access MVP
"Peter Miller" <pmi...@pksolutions.com> wrote in message
news:5klveucdhu2bd1vaq...@4ax.com...
John WInterbottom
news:uPG54tEBCHA.1836@tkmsftngp05...
>
> SQL Server doesn't allow you to group by a TEXT field (similar to Access
> MEMO fields)
>
No, but it does allow 8,000 characters, (version 7.0) in a CHAR or VARCHAR
column - which means that a lot of very long text fields can be stored as
CHAR data. The 255 character limit in Jet means that you don't have that
option, so memo is your only alternative.
Duane Hookom
characters?
--
Duane Hookom
Microsoft Access MVP
"John WInterbottom" <jo...@assaynet.com> wrote in message
news:3UWH8.22880$Mn6.2...@news20.bellglobal.com...
Tom Ellison
would be if all its paragraphs were arrange in alphabetical order!
Tom Ellison
Microsoft Access MVP
John WInterbottom
> So, are you suggesting that you have a need to sort and group by 8,000
> characters?
>
I'm suggesting that it's not inconceivable to want to group on a column that
may hold a couple of hundred characters - which, I guess, is why MS decided
it was a good idea to increase the size of the data type in SQL Server.
Duane Hookom
would guess that MS had to draw the line at some number of characters in
order to maintain performance or whatever. There are probably trade-offs
with the 255 or 8,000. In about 8 years of working approximately 40 hours a
week with Access (and SQL Server) as well as reading messages on various
news groups, I have never come across a legitimate reason for sorting or
grouping by more than a couple dozen characters.
--
Duane Hookom
Microsoft Access MVP
"John WInterbottom" <jo...@assaynet.com> wrote in message
news:iEZH8.13761$qn1.3...@news20.bellglobal.com...
david @ epsom dot com dot au
is a repeated irritation, as is
'Can't have <Memo> fields in criteria for aggregate functions'
and even
'Can't use <Memo> field in the SELECT clause of a union query'
Of course you can't have memo fields for criteria for aggregate
functions because the Group clause runs before the Where
clause (have I got that right?), so the ability to group on memo
fields does have a certain limited amount of appeal.
And yes, in a perfect world I would like to be able to index
memo fields and use them as join fields: even if I never put
more than three characters into the field, why should the
design be tied to the physical representation used by the
database engine? (Can you pick which programming language
is my least favourite?)
(david)
"Duane Hookom" <Duane...@mvps.org> wrote in message news:#O1bcbFBCHA.2656@tkmsftngp05...
John WInterbottom
> A "couple of hundred characters" I believe is 200 which Access allows. I
> would guess that MS had to draw the line at some number of characters in
> order to maintain performance or whatever. There are probably trade-offs
> with the 255 or 8,000. In about 8 years of working approximately 40 hours
a
> week with Access (and SQL Server) as well as reading messages on various
> news groups, I have never come across a legitimate reason for sorting or
> grouping by more than a couple dozen characters.
>
Well, I have to grant you, it wouldn't be something at the top of my Access
wish list <g>
dear...@gmail.com
> with the 255 or 8,000. In about 8 years of working approximately 40 hours a
> week with Access (and SQL Server) as well as reading messages on various
> news groups, I have never come across a legitimate reason for sorting or
> grouping by more than a couple dozen characters.