If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Sequential Grouping
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 |
#3
|
|||
|
|||
Sequential Grouping
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] |
#4
|
|||
|
|||
Sequential Grouping
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 |
#5
|
|||
|
|||
Sequential Grouping
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] |
Thread Tools | |
Display Modes | |
|
|