J
James Walker
I'm using Access 2003 and was wondering if this is possible. I have a query
(call it Query1) that returns an ID along with FirstName, LastName, and a
Year. I would like a second query (or macro) that would "group" the Years
into a range. However I would like the range to be "broken" if the years are
not consecutive.
Example of Query1 results:
ID FirstName LastName Year
1 John Doe 1999
1 John Doe 2000
1 John Doe 2001
2 Jane Deer 1983
2 Jane Deer 1984
2 Jane Deer 1986
3 Joe Buck 1999
3 Joe Buck 2007
3 Joe Buck 2008
4 Julie Fawn 2003
5 Kitty Smith 1995
5 Kitty Smith 1996
5 Kitty Smith 2000
5 Kitty Smith 2001
Example of output of new query (or macro):
ID FirstName LastName YearRange
1 John Doe 1999-2001
2 Jane Deer 1983-1984, 1986
3 Joe Buck 1999, 2007-2008
4 Julie Fawn 2003
5 Kitty Smith 1995-1996, 2000-2001
Any suggestions on how to get this accomplished would be great.
Thanks,
James Walker
(call it Query1) that returns an ID along with FirstName, LastName, and a
Year. I would like a second query (or macro) that would "group" the Years
into a range. However I would like the range to be "broken" if the years are
not consecutive.
Example of Query1 results:
ID FirstName LastName Year
1 John Doe 1999
1 John Doe 2000
1 John Doe 2001
2 Jane Deer 1983
2 Jane Deer 1984
2 Jane Deer 1986
3 Joe Buck 1999
3 Joe Buck 2007
3 Joe Buck 2008
4 Julie Fawn 2003
5 Kitty Smith 1995
5 Kitty Smith 1996
5 Kitty Smith 2000
5 Kitty Smith 2001
Example of output of new query (or macro):
ID FirstName LastName YearRange
1 John Doe 1999-2001
2 Jane Deer 1983-1984, 1986
3 Joe Buck 1999, 2007-2008
4 Julie Fawn 2003
5 Kitty Smith 1995-1996, 2000-2001
Any suggestions on how to get this accomplished would be great.
Thanks,
James Walker