Help with Query (or Macro??)

[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

 

[Michel Walsh]

Yes, with a couple of queries.

The first query compute the rank, within the group of ID:

SELECT a.id, a.year, COUNT(*) AS rank
FROM tableName AS a INNER JOIN tableName As b
ON a.id=b.id AND a.year >= b.year
GROUP BY a.id, a.year


the second query return the min and max year of each un-interrupted
sequences:


SELECT id, MIN(year), iif(MAX(year)= MIN(year), null, MAX(year))
FROM previousQuery
GROUP BY id, year-rank


which should return:

( the name is not returned by the query, only the ID is, but I add
the name for convenience)
1 (John Doe) 1999 2001
2 (Jane Deer) 1983 1984
2 1986 null
3 (Joe Buck) 1999 null
3 2007 2008
4 (Julie Fawn) 2003 null
5 (Kitty Smith) 1995 1996
5 2000 2001



You can add the name with a simple inner join.

Note that there is one record per un-interrupted sequence, and if the upper
limit is the same as the lower limit, a NULL is returned rather than
repeating the lower limit of the range..




Vanderghast, Access MVP

 

[James Walker]

Thanks for the reply. My data is now if the format you've shown, however
there are still multiple ID shown. Is there another query or macro that can
"group" so that each ID is unique in the final output?

Your return values:
ID MinYear MaxYear
1 1999 2001
2 1983 1984
2 1986 <NULL>
3 1999 <NULL>
3 2007 2008
4 2003 <NULL>
5 1995 1996
5 2000 2001


Final Goal return values:
ID YearRange
1 1999-2001
2 1983-1984, 1986
3 1999, 2007-2008
4 2003
5 1995-1996, 2000-2001

Thanks again for the help,
James Walker

 

[Michel Walsh]

You will have to concatenate the strings made of:

MinYear & ("-" + MaxYear )


That can be done in many ways, I generally use a temp table for that. First,
create a temporary table, say tmpConcat, two fields, id (number) and
concat (char(255). Fill the table with all possible id values, leaving
concat field empty (null):

tmpConcat
id concat
1 <null>
2 <null>
3 <null>
4 <null>
5 <null>



which can be done (creation of the table and filling) with something like:


SELECT id, IIf(False," ",Null) AS concat
INTO tmpConcat
FROM yourInitialTableHere
GROUP BY id;



then, use the following update query:


UPDATE tmpConcat INNER JOIN queriesWithRanges
ON tmpConcat.id = queriesWithRanges.id
SET tmpConcat.concat = (tmpConcat.concat + ", " ) & MinYear & ("-" +
MaxYear)




The result is in the temporary table.




Vanderghast, Access MVP