Group By Query (Access 97)

[Jim Allen]

I'm a novice when it comes to SQL. I have a query where
I'd like to use the Group By clause. However, I also
have a memo field as part of this query. When I use
group by Access tells me the memo field isn't part of the
aggregate. When I add the field to the aggregate,
Access tells me I cannot group by a memo field. How do I
get around this? Any suggestions would be appreciated.
Thanks.
Jim

 

[John Vinson]

I'm a novice when it comes to SQL. I have a query where
I'd like to use the Group By clause. However, I also
have a memo field as part of this query. When I use
group by Access tells me the memo field isn't part of the
aggregate. When I add the field to the aggregate,
Access tells me I cannot group by a memo field. How do I
get around this? Any suggestions would be appreciated.
Thanks.
Jim

Do you really need to *GROUP BY* the memo field - i.e. will you have
several records which are identical for all the fields except the
memo? If not, you can use First() as the aggregate function for the
memo field.

If you do need to group by it, the best you can do is create a
calculated field:

MemoPart: Left([memofield], 255)

Group By that and again use First() as the memo aggregate function. If
two records differ only after the 255th byte of the memo field I can't
think of a good way to group by them.

 

[Guest]

Thanks. No I don't need to group by the memo field, I
need to group by other fields in the query. In the
future I'll try to be more clear. I'll give the First()
function a shot. Thanks for the help.

-----Original Message-----

I'm a novice when it comes to SQL. I have a query where
I'd like to use the Group By clause. However, I also
have a memo field as part of this query. When I use
group by Access tells me the memo field isn't part of the
aggregate. When I add the field to the aggregate,
Access tells me I cannot group by a memo field. How do I
get around this? Any suggestions would be appreciated.
Thanks.
Jim

Do you really need to *GROUP BY* the memo field - i.e. will you have
several records which are identical for all the fields except the
memo? If not, you can use First() as the aggregate function for the
memo field.

If you do need to group by it, the best you can do is create a
calculated field:

MemoPart: Left([memofield], 255)

Group By that and again use First() as the memo aggregate function. If
two records differ only after the 255th byte of the memo field I can't
think of a good way to group by them.


.