Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 01-31-2002, 05:44 PM
Rockhound's Avatar
Rockhound Rockhound is offline
Site Supporter
Site Supporter
SOA AAA
 
Join Date: Sep 2001
Location: America the Beautiful
Studying for retirement
Favorite beer: Whatever is on special
Posts: 5,542
Default

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?
Reply With Quote
  #2  
Old 01-31-2002, 07:12 PM
CallMeCrazy CallMeCrazy is offline
 
Join Date: Nov 2001
Posts: 26
Default

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.
Reply With Quote
  #3  
Old 01-31-2002, 10:18 PM
Rockhound's Avatar
Rockhound Rockhound is offline
Site Supporter
Site Supporter
SOA AAA
 
Join Date: Sep 2001
Location: America the Beautiful
Studying for retirement
Favorite beer: Whatever is on special
Posts: 5,542
Default

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>
Reply With Quote
  #4  
Old 02-01-2002, 11:45 AM
Toll Free Toll Free is offline
Member
 
Join Date: Nov 2001
Posts: 57
Default

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.
Reply With Quote
  #5  
Old 02-01-2002, 12:29 PM
Rockhound's Avatar
Rockhound Rockhound is offline
Site Supporter
Site Supporter
SOA AAA
 
Join Date: Sep 2001
Location: America the Beautiful
Studying for retirement
Favorite beer: Whatever is on special
Posts: 5,542
Default

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?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 11:07 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.20360 seconds with 11 queries