Group Consecutive Numbers in a Range

[ChiSoxFan]

Hello,

Looking for a way to group consecutive numbers in a range.

Here is a some sample data from a table (tblYears)

Id	Year
smith01	2003
smith01	2004
smith01	2005
smith01	2006
jones01	2004
jones01	2005
jones01	2007
jones01	2009
davis01	2006

If possible, I would like a query that would return the following:

Id	Years
smith01	2003-2006
jones01	2004-2005, 2007, 2009
davis01	2006

I am open to any suggestions, as I have no idea where to start. Can this be done with a query only, do I need a temp table?


Thanks,
ChiSoxFan

[JoeM]

The only way I can envision doing something like that is to sort the data in a query, use VBA and Recordsets (i.e. DAO) to loop through the data row-by-row, and write the results out to a new table.

So to answer your question, I think you will need VBA and a new table.