Sequential Grouping

[blobmiester via AccessMonster.com]

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

 

[blobmiester via 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

 

[John W. Vinson]

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

 

[blobmiester via AccessMonster.com]

Let me try and clarify more:

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.

 

[John W. Vinson]

Let me try and clarify more:

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?