Separate Sets of Consecutive Numbers

[samcase2]

Hi there!

I am working with a set of numbers all under the same ID in a table. Based on the ID number, these values all appear (as a list) on a report I created; however, since there are so many, I want to simply put a minimum and a maximum. The problem is, the numbers fall in consecutive sets, and I don't want the min and max to include the skipped values. So, for example, if there are a total of 30 numbers, they may fall something like 1-10, then 25-30, then 40-50, then 75-80. I'm looking for a way to show a minimum and a maximum for each consecutive set, like I just did, preferrably using a function or operator of some sort on the report and not modifying a the original data table. Any help?

Thanks!!

[June7]

How many sets, how high does this numbering go?

Create a group section in report with calc (or do the calc in query to create a field for grouping).

Switch([fieldname]<11, "1-10", [fieldname] BETWEEN 25 AND 30, "25-30", [fieldname] BETWEEN 40 AND 50, "40-50", [fieldname] BETWEEN 75 AND 80, "75-80", [fieldname]>80, ">80")

Now expressions in textboxes in group footer: Min([fieldname]), Max([fieldname])

What about values between 10 and 25, 30 and 40, 50 and 75?

[samcase2]

That would work, except I have hundreds of number sets, and the values range anywhere from 10635 to 31783. There is no consistency in how many numbers are in each consecutive group, and there is no limit how how many consecutive groups there could be for each ID. Is there a way to create group sections like you said, just with the calculation or criteria being "consecutive numbers only", so that each group section contains only consecutive numbers?

[June7]

This reminds me of 'natural breaks' data classification in ArcGIS mapping software. If it can be done within a query statement, it's beyond my knowledge and abilities. http://support.esri.com/en/knowledge...classification

[ssanfu]

Well...... not knowing what your table or report looks like and not having what the results should look like, I threw together an example of a brute force method using VBA. Don't know if it will help, but maybe it will give you some ideas...