Help with intervals...

[Chris]

I'm creating a report takes data from a table, then
performs the calculations to get what it is I need. The
problem I have run into is with the intervals that this
data is grouped on. For the most part I want it in
intervals of 50. But there are a few other categories with
are not exactly 50, and this is where my problem is.

I want to group the data like this:
- less than 300
- 300-349
- 350-399
..
..
..
- 900-949
- 950-997
- 998-999

What I've done so far is just create an if statement to
actually display these intervals, and set, in sorting and
grouping the following:
Group Header - Yes
Group Footer - Yes
Group On - Interval
Group Interval - 50
Keep Together - Whole Group

Any help in this getting my report to group this way would
be great.
Thanks

 

[Chris]

Oh and I know that this is the reason why I am total
groups of fifty and no less. I just need a way to do those
3 intervals that are not fifty along with the rest being
by fifty...
Hope that makes sense...

 

[Marshall Barton]

I'm creating a report takes data from a table, then
performs the calculations to get what it is I need. The
problem I have run into is with the intervals that this
data is grouped on. For the most part I want it in
intervals of 50. But there are a few other categories with
are not exactly 50, and this is where my problem is.

I want to group the data like this:
- less than 300
- 300-349
- 350-399
.
.
.
- 900-949
- 950-997
- 998-999

What I've done so far is just create an if statement to
actually display these intervals, and set, in sorting and
grouping the following:
Group Header - Yes
Group Footer - Yes
Group On - Interval
Group Interval - 50
Keep Together - Whole Group

Any help in this getting my report to group this way would
be great.

The way I would approach the problem of irregular group
intervals is to create a table that defines the interval
ranges along with some oter helper value(s?).

Let's say you have a table named CatIntervals with fields
IntervalNum
CategoryID
RangeStart
RangeEnd

some sample data might look like:
IntervalNum CategoryID RangeStart RangeEnd
1 1 0 300
2 1 301 350
. . .
14 1 950 997
15 1 998 999

Now you can add that information to the reports record
source query:

SELECT table.*, CatIntervals.IntervalNum,
CatIntervals.RangeStart, CatIntervals.RangeEnd
FROM table INNER JOIN CatIntervals ON table.fieldx Between
CatIntervals.RangeStart And CatIntervals.RangeEnd
WHERE CatIntervals.CategoryID = 1

The report can now group on the IntervalNum field and the
range limits are available to display in the group header.