Grouping Interval

[Guest]

I want to group data in a report based on an ID number that is formated as
double and is 7 digits long. I want to group the data in sets of 10. When I
set the grouping interval to 10 for this feild it groups them based on the
10' place digit, for example: 5100016, 5100017, 5100018, 5100019 would be
grouped together but 5100020, 5100021, 5100022, 5100022, 5100023, 5100024,
5100025 would be grouped separtely. I want these 10 to be grouped together,
then the next 10. How can this be accomplished?

Thanks,
Dan

 

[Marshall Barton]

I want to group data in a report based on an ID number that is formated as
double and is 7 digits long. I want to group the data in sets of 10. When I
set the grouping interval to 10 for this feild it groups them based on the
10' place digit, for example: 5100016, 5100017, 5100018, 5100019 would be
grouped together but 5100020, 5100021, 5100022, 5100022, 5100023, 5100024,
5100025 would be grouped separtely. I want these 10 to be grouped together,
then the next 10. How can this be accomplished?

 

[Marshall Barton]

I want to group data in a report based on an ID number that is formated as
double and is 7 digits long. I want to group the data in sets of 10. When I
set the grouping interval to 10 for this feild it groups them based on the
10' place digit, for example: 5100016, 5100017, 5100018, 5100019 would be
grouped together but 5100020, 5100021, 5100022, 5100022, 5100023, 5100024,
5100025 would be grouped separtely. I want these 10 to be grouped together,
then the next 10. How can this be accomplished?

Whoops, hit the wrong button.

That's what the group interval does. If you want to group
on the number of items, you need to create the grouping
value in the report's record source query so the report can
use it.

As long as your numbers are unique, you can create a new
query based your current query.

SELECT yourquery.*,
(SELECT Count(*)
FROM yourquery As X
WHERE X.ID > yourquery.ID) \ 10 As GrpNum
FROM yourquery

Then set the report to group on the GrpNum field.

 

[Guest]

Marshall,
I created the querey like you suggested and it seemed to work, however
when I changed the report to group on the grpnum and tried to preview the
report, Access crashes! I only have 20 records in the trial table, surely it
can not be to complicated for access to compute. Any Suggestions?

Thanks,
Dan

 

[Marshall Barton]

I thought they fixed that problem. The last time I ran into
it, it didn't crash, but it did today (A2003).

Oh well, the old mysterious incantation still worked in my
test:

SELECT yourquery.*,
(SELECT Count(*)
FROM yourquery As X
WHERE X.ID > yourquery.ID) \ 10 As GrpNum
FROM yourquery
UNION ALL
SELECT *, 0 FROM yourquery WHERE ID = 0