Posted by Erland Sommarskog on 03/04/07 10:24

Chris (chrislabs12@gmail.com) writes:
> I have a table which lists player names, teams played for and the
> years they played there and my code looks like this
>
> SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
> AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
> Max([AlsoPlayedFor].[Year]) AS [Year]
> FROM AlsoPlayedFor
> GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
> AlsoPlayedFor.TeamName;
>
> which takes the Min year and the Max Year and displays it like "Year-
> Year"
>
> But lets say for example the player played for 5 years so it 1990,
> 1991, 1992, 1993, 1995
>
> It would display as 1990-1995 but I want it to display as 1990-1993,
> 1995, is this possiable??? Also I need it to gothe other wayso if the
> years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
> 1992-1995.

I could suggest a query which in SQL 2005 at least give you a comma-
separated list of the years. Collapsing adjacent years into ranges appears
to make things a lot more complicated.

However, the query you posted has syntax which is not legal in SQL Server,
but has a touch of Access, a product of which I have no experience.

Could you clarify which product and which version of that product you
are using? If you are using Access, I recommend that you try an Access
newsgroup instead.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]