Access Queston

[Rockhound]

I'm pretty much of a novice at Access (or any data base application) and I only use it for fun. I've got some statistics for a fantasy sports league that I maintain and would like to do the following:

I have a record for each player for each season, with fields for each of his statistics for the season, including a field for the team he played for that particular season. I want to create a queary that will combine all of the different teams a player played for into one field, so that when I calculate the players' career totals, I have one field that lists all of the teams he's ever been on.

Thoughts on how to do this?

[CallMeCrazy]

To keep things simple, let's assume that your database has the following five fields: Name, Team, Season, Hits, HR

To obtain career totals for each player, create a new query and:
(1) drag the "Name" field down (and leave the "Group By" setting;
(2) drag the "Hits" field down and change "Group By" to "Sum";
(3) drag the "HR" field down and change "Group By" to "Sum".

If you want to know how many seasons they played, drag "Season" down and change "Group By" to "Count"

If you want the career totals split amongst teams, then drag "Team" down (and leave the "Group By" setting).

I don't know if this answer is very clear, but hopefully it will get you pointed in the right direction. Good luck.

[Rockhound]

Thanks for the quick reply.

Actually I've been able to get that far (maybe I'm slightly above "novice"). Continuing your example, lets say the only records we have are for 2 players and they look like:

Rockhound LA 1999 250 15
Rockhound LA 2000 130 10
Rockhound SF 2001 170 25
Callmecrazy NY 1999 310 30
Callmecrazy SL 2000 290 20
Callmecrazy Bo 2001 200 25


I want a query that will produce career records looking something like:

Player-------Teams-----1st----Last--Hits--HR

Rockhound----LA,SF-----1999---2001--500---50
Callmecrazy--NY,SL,Bo--1999-- 2001--800---75

I can get everything except combining the teams into one field (the "LA,SF" and the "NY,SL,Bo")

<font size=-1>[ This Message was edited by: Rockhound on 2002-01-31 21:21 ]</font>

[Toll Free]

Try this:

1) Write a crosstab query, counting "teams" by player name.

2) Write a report listing all teams by player, and figure out (there's a way, I just don't know off the top of my head) how to skip a field if it's null. Include combined stats via the suggestion above.

[Rockhound]

Thanks, good thoughts.

I'd fooled with that approach some. I can get a crosstab with lots of ones and zeros (indicating that the player played with that team). To get the teams into one field, I then used an "if statement" that if say the NY field had a value greater than 1, show "NY" and then concatenate (&) to an if statement about the LA field and so on.

The difficulty of that, (at least the way I'm extracting from the cross-tab) is that I need to know (and manually key in) all of the team names into a very long statement in the query. If a new team name is added, then I have to rewrite my query to include that team, as it will be a new column in the cross tab. Is there a way to access the cross tab column names (which is in effect the data in the Teamname field) to put into the query design?