What is the limit to grouping categories in a Query?

[Michael]

Here's a question I am getting from someone I am trying to help. She is
using Access 97, works for an HMO, and there are a huge number of records. I
don't understand the question, can anyone else understand and answer it?

"When you select more than x variables to be in your query there seems to be
a limit on how many you can group that query by. After grouping by x
variables you need to either not group them at all or get creative with the
other available 'total' commands. I just wanted to find out what this limit
is and if there is a way of getting around it."

I may not have enough experience to know what's she talking about. Thanks!

 

[Joseph Meehan]

Here's a question I am getting from someone I am trying to help. She
is using Access 97, works for an HMO, and there are a huge number of
records. I don't understand the question, can anyone else understand
and answer it?
"When you select more than x variables to be in your query there
seems to be a limit on how many you can group that query by. After
grouping by x variables you need to either not group them at all or
get creative with the other available 'total' commands. I just
wanted to find out what this limit is and if there is a way of
getting around it."
I may not have enough experience to know what's she talking about.
Thanks!

I think I know what she is talking about. I would guess she is running
up against some sort of limit to the number of "Fields" that can be
grouped on. My guess is the limit if there is one would be 255 total
characters. but I could be totally wrong. I don't have a copy of 97 on any
machines any more, in fact I may have thrown mine away so I can't confirm
it.

 

[Allen Browne]

Access permits up to 255 fields in a query. There is no other published
limit on the number of fields in the GROUP BY clause.

There is no limit on the number of values in any particular field in the
GROUP BY clause. In a well designed data structure, Access will cope with
many thousands (even hundreds of thousands) of values.

There are many things you can do to make the Totals query more efficient,
such as:
- indexing the fields used in sorting, grouping, and criteria;
- avoiding wildcards (particularly leading wilcards);
- using a WHERE clause to eliminate values before selection, rather than a
HAVING clause to eliminate after grouping;
- using First instead of Group By for memo fields and fields from lookup
tables that will be the same anyway;
- simplifying the criteria (e.g. avoiding circumlocutions such as including
lots of references to form controls and then trying to get it to return
values when those controls are null anyway.)

 

[Joseph Meehan]

Here's a question I am getting from someone I am trying to help. She
is using Access 97, works for an HMO, and there are a huge number of
records. I don't understand the question, can anyone else understand
and answer it?
"When you select more than x variables to be in your query there
seems to be a limit on how many you can group that query by. After
grouping by x variables you need to either not group them at all or
get creative with the other available 'total' commands. I just
wanted to find out what this limit is and if there is a way of
getting around it."
I may not have enough experience to know what's she talking about.
Thanks!

I might add, on a second thought, that I wonder if this might really be
a normalization issue. Just a thought.

 

[Joseph Meehan]

Here's a question I am getting from someone I am trying to help. She
is using Access 97, works for an HMO, and there are a huge number of
records. I don't understand the question, can anyone else understand
and answer it?
"When you select more than x variables to be in your query there
seems to be a limit on how many you can group that query by. After
grouping by x variables you need to either not group them at all or
get creative with the other available 'total' commands. I just
wanted to find out what this limit is and if there is a way of
getting around it."
I may not have enough experience to know what's she talking about.
Thanks!

Would any of those fields be memo type?

 

[Rick Brandt]

Access permits up to 255 fields in a query. There is no other
published limit on the number of fields in the GROUP BY clause.

There is no limit on the number of values in any particular field in
the GROUP BY clause. In a well designed data structure, Access will
cope with many thousands (even hundreds of thousands) of values.

It's not in the specifications topic but in Access 97 the limit on the number of
fields you can group on appears to be 10. The help topic for the error
message...

You created an SQL statement with more than 10 fields listed in the GROUP BY
clause. Remove some of the fields listed in the GROUP BY clause.