By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

query help with conditional summing

P: n/a
I have a database with a table named Fielding that contains (among
others) the following records and fields:

playerID = unique with records for each change in POS, year, team

POS = P, C, 1B, 2B, 3B, SS, LF, CF, RF, OF, DH

Games = number of games played at each POS

For example, using this data:

playerID yearID stint teamID POS Games
-----------------------------------------------------
lakeed01 1943 1 BOS SS 63
lakeed01 1944 1 BOS 2B 3
lakeed01 1944 1 BOS 3B 1
lakeed01 1944 1 BOS P 6
lakeed01 1944 1 BOS SS 41
lakeed01 1945 1 BOS 2B 1
lakeed01 1945 1 BOS SS 130
lakeed01 1946 1 DET SS 155
lakeed01 1947 1 DET SS 158
lakeed01 1948 1 DET 2B 45

I need an output like this for each playerID:

playerID =P <>P
---------------------------
lakeed01 6 597

Can this be done straightforwardly? Thanks,
Cliff
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You can use a crosstab query like this:

TRANSFORM Sum(Fielding.Games) AS SumOfGames
SELECT Fielding.PlayerID
FROM Fielding
GROUP BY Fielding.PlayerID
PIVOT [POS]="P";

HTH
- Turtle
"Cliff" <ba*********@hotmail.com> wrote in message
news:a5**************************@posting.google.c om...
I have a database with a table named Fielding that contains (among
others) the following records and fields:

playerID = unique with records for each change in POS, year, team

POS = P, C, 1B, 2B, 3B, SS, LF, CF, RF, OF, DH

Games = number of games played at each POS

For example, using this data:

playerID yearID stint teamID POS Games
-----------------------------------------------------
lakeed01 1943 1 BOS SS 63
lakeed01 1944 1 BOS 2B 3
lakeed01 1944 1 BOS 3B 1
lakeed01 1944 1 BOS P 6
lakeed01 1944 1 BOS SS 41
lakeed01 1945 1 BOS 2B 1
lakeed01 1945 1 BOS SS 130
lakeed01 1946 1 DET SS 155
lakeed01 1947 1 DET SS 158
lakeed01 1948 1 DET 2B 45

I need an output like this for each playerID:

playerID =P <>P
---------------------------
lakeed01 6 597

Can this be done straightforwardly? Thanks,
Cliff

Nov 13 '05 #2

P: n/a
"MacDermott" <ma********@nospam.com> wrote in message news:<gS*****************@newsread3.news.atl.earth link.net>...
You can use a crosstab query like this:

TRANSFORM Sum(Fielding.Games) AS SumOfGames
SELECT Fielding.PlayerID
FROM Fielding
GROUP BY Fielding.PlayerID
PIVOT [POS]="P";


Thanks, Turtle. That got me on track. Didn't know about pivot tables and Access.

Cliff
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.