Sequential Grouping

Hey all,

I have a column in a table that goes something like this:

1
2
3
5
6
7
8

And I want to group the sequential numbers together like this:

1 - 3 : Group 1
5 : Group 2
6 - 8 : Group 3

Is there any reasonable way this can be done?

Thank you in advance,

Ryan Leckey

--
Message posted via http://www.accessmonster.com

Just for clarifiaction, I meant:

1 - 3: Group 1
5 - 8: Group 2

and if there was a 10 at the bottom then like this

1 - 3 : Group 1
5 - 8 : Group 2
10 : Group 3

Thank you

--
Message posted via http://www.accessmonster.com

On Thu, 11 Mar 2010 22:34:13 GMT, "blobmiester via AccessMonster.com"
u58685@uwe wrote:

Hey all,

I have a column in a table that goes something like this:

1
2
3
5
6
7
8

And I want to group the sequential numbers together like this:

1 - 3 : Group 1
5 : Group 2
6 - 8 : Group 3

Is there any reasonable way this can be done?

Thank you in advance,

Ryan Leckey

If the grouping is arbitrary (i.e. not something you can calculate with an
arithmatic expression), your best bet is to use a Ranges table like:

Ranges
RangeName primary key, text, e.g. "Group 1"
Lo number, matching your table
Hi ditto

You can then use a "Non equi Join" query to look up the rangename:

SELECT yourtable.somefields, Ranges.RangeName
FROM yourtable
INNER JOIN Ranges
ON Ranges.Lo = yourtable.columnname
AND Ranges.Hi = yourtable.columnname

--

John W. Vinson [MVP]

Let me try and clarify mo

Here is the actual data:

1 2002
1 2003
1 2004
1 2006
1 2008
1 2009
2 2002
2 2003
3 2004
3 2005

I don't know what the years will be. But if there is multiple years in a row
I would like to collapse them in a range (grouping).

So the table above would be come this:

1 2002 - 2004
1 2006
1 2008 - 2009
2 2002 - 2003
3 2004 - 2005

Is that clearer? Sorry, if I wasn't clear before.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201003/1

On Thu, 11 Mar 2010 23:41:15 GMT, "blobmiester via AccessMonster.com"
u58685@uwe wrote:

Let me try and clarify mo

Here is the actual data:

1 2002
1 2003
1 2004
1 2006
1 2008
1 2009
2 2002
2 2003
3 2004
3 2005

I don't know what the years will be. But if there is multiple years in a row
I would like to collapse them in a range (grouping).

So the table above would be come this:

1 2002 - 2004
1 2006
1 2008 - 2009
2 2002 - 2003
3 2004 - 2005

Is that clearer? Sorry, if I wasn't clear before.

Well, it's clearer and it's altogether a different and unrelated problem (you
might want to reread your initial post after clearing your mind of
preconceptions about the database)....

That's going to be difficult and will require some VBA code, I'd guess. You
will need to find all records for each ID (or whatever the 1, 2 field
represents), sort them in order, and step through looking for gaps in the
sequence.

What will you be doing with the result? Storing it in a Table, generating a
report, or what? What's the context?

--

John W. Vinson [MVP]