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
_____________________________________________________
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...
>
> 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
Microsoft Access MVP
"John WInterbottom" <jo...@assaynet.com> wrote in message
news:3UWH8.22880$Mn6.2...@news20.bellglobal.com...
Tom Ellison
Microsoft Access MVP
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
Microsoft Access MVP
"John WInterbottom" <jo...@assaynet.com> wrote in message
news:iEZH8.13761$qn1.3...@news20.bellglobal.com...
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...
Well, I have to grant you, it wouldn't be something at the top of my Access
wish list <g>