Why can't you include a MEMO field while grouping? 2

[dschomburg]

Just courious.

 

[Steve101]

Can't group by it essentially because its not a "fixed length". The designers of the language elected to not allow grouping by memo's because the memo data type is intended to hold verbose textual data and as such it does'nt seem sensible to group by this sort of unstructured "random" data. It would also be very run-time inefficient.
If you really need this sort of functionality in an existing database, and you are sure that your memo data is sensible enough to make grouping by meaningful, then you can convert (part of) the memo to a string, and then apply the group by to the string.

For example, if your memo is called memo1, and you know that the first 50 characters have a certain structure, then you can Group By LEFT(memo1, 50)

Hope this helps,
Steve

 

[dschomburg]

Hey thanks Steve.
I've always known you can't but just didn't know why.

Since a memo type has no fixed length, how much room does Access set aside when you use a memo?
I.E 2 bits. 10. whatever.

Again just wondering.

Dave

 

[RottPaws]

This is a topic I've wanted to ask about forever, but I have never actually gotten around to posting . . . .

Is there a way to include a memo field in the results of a query that is grouped? I don't want to group on the memo field, just include it.

For example, I have a comments table that includes order_id (long), Comment_date (date), User_ID (text), and Comment_Text (memo) fields.

I'd like to be able to retrieve the latest comment for each order in one query, but grouping is not allowed on the memo field. What I've done to get around this in the past is run one query --GroupBy Order_id and Max(Comment_Date)-- that dumps the order_ids and comment dates into a table and then I run an update query to pull the comment text and user id for each of those. Then I can run a query on that table to put the information into a recordset . . .

Is there a better way? _________
Rott Paws

...It's not a bug. It's an undocumented feature.

 

[Steve101]

Dave,
According to the Access (97) online help, a memo field can contain 1.2 GB of data. My guess is that the size of the pointer used then is 8 bytes (though I hav'nt done the arithmentic).

Rott Paws,
You cant perform arithmentic or logical operators or functions on memo fields; only string type functions; thus in an aggregate query, dont believe that any of the aggregate functions will work directly on a memo field. What you CAN do, is covert (part of) the memo field to a string, for example using the LEFT function, and then possibly use these results in the aggregate function.

eg. SELECT ProblemId, LAST(LEFT(Comments,50)) AS CommentPrefix
FROM tblProblem
GROUP BY ProblemId

Give this a go,
Cheers,
Steve

 

[dschomburg]

Thanks a heap Steve. I am now a wiser person.

 

[RottPaws]

Thanks for the info. I guess I'll just keep doing it the way I have been . . . _________
Rott Paws

...It's not a bug. It's an undocumented feature.

 

[iq193]

You can include a memo field in a 'group' clause. The trick is to make Access treat it as something other than a memo.

to do this simply prepend a null to the memo field. For example if your memo field was named 'COMMENTS' then your group clause would resemble: group by ''+COMMENTS.

This also works for the 'ORDER BY' clause.

Using this method ensures you retrieve the entire contents of the field.

 

[RottPaws]

Interesting......I'll have to remember that the next time I have a need for this.

Thanks for the tip. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[dschomburg]

hmmm... I could not get it to work. I tried:
SELECT test.sex, +memofield
FROM test
GROUP BY test.sex, +memofield;

by the way, the "sex" field is just male or female.

 

[iq193]

you forgot to prepend a null in your clause it should read:

select test.sex, ''+memofield
from test
group by test.sex, ''+memofield


the 2 quotes are the null character that you are prepending, this forces Access to treat it as a fixed length string

 

[RottPaws]

I haven't tried this yet, but from that iq193 wrote above, it looks like you need 2 single quotes before the memo field.

Try:

SELECT text.sex, memofield
FROM test
GROUP BY text.sex, '' + memofield;

You may have to put
'' + memofield as Memo
in the select statement.

I'm just guessing. You may have to experiment a little . . . . _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!