Calculating a sum in a query

[Crazybrit]

Hi,
I Have created a database for pool league statistics and would with various tables similar to this schema http://www.databaseanswers.org/data_...tics/index.htm
The Result Code is populated with 1 - Home Win or 2 Away Win and i want to create a query that returns a list of players and how many wins they have I Cant seem to figure out how to go about this any help will be much appreciated i have attached a copy of the database in zip format thanks again
Kind Regards Crazybrit

[June7]

Advise not to have Lookups in tables. Review http://access.mvps.org/access/lookupfields.htm

See if this is what you want:

Query1:
SELECT "Home" As Side, HomePlayer1 As Player, Choose(Result_Code, "Win", "Loss") As Place FROM Games
UNION ALL SELECT "Home", HomePlayer2, Choose(Result_Code, "Win", "Loss") FROM Games
UNION ALL SELECT "Away", AwayPlayer1, Choose(Result_Code, "Loss", "Win") FROM Games
UNION ALL SELECT "Away", AwayPlayer2, Choose(Result_Code, "Loss", "Win") FROM Games;

There is no wizard or designer for UNION, must type (or copy/paste) in the SQL view window of query designer.

DoublesGameID record 29 is missing Result_Code. The Choose function will error until this is corrected or use:
Choose(Nz(Result_Code,3), ..., ..., "none")

Query2:
SELECT Query1.Player, Sum(IIf([Place]="Win",1,0)) AS Wins, Sum(IIf([Place]="Loss",1,0)) AS Losses
FROM Query1
GROUP BY Query1.Player;

[Crazybrit]

Oh Thats brilliant thanks i have made some adjustments because i neglected to say i didnt want the doubles games included in the query but nontheless your answer helped me out fabulously i wonder if you could help me further i also need to make a query that lists points for teams per season however the points are calulated on a point for each game won and 3 points Extra for an Away Match win and 2 Points Extra for a HomeMatch win Thanks again Crazybrit

[June7]

What determines a match win? Best of 5 games?

Modify the UNION (adjusted to exclude 'doubles'):
SELECT MatchID, DoublesGameID, "Home" As Side, HomePlayer1 As Player, Choose(Result_Code, "Win", "Loss") As Place FROM Games WHERE HomePlayer2 Is Null And AwayPlayer2 Is Null
UNION ALL SELECT MatchID, DoublesGameID, "Away", AwayPlayer1, Choose(Result_Code, "Loss", "Win") FROM Games WHERE HomePlayer2 Is Null And AwayPlayer2 Is Null;

Join that query to other tables:
SELECT Year([Match_date]) AS Yr, Matches.Season, Query1.MatchID, Query1.DoublesGameID, Team.team_ID, Query1.Player, Query1.Side, Query1.Place
FROM Team INNER JOIN (Players INNER JOIN (Matches INNER JOIN Query1 ON Matches.MatchID = Query1.MatchID) ON Players.Player_ID = Query1.Player) ON Team.team_ID = Players.TeamID
ORDER BY Year([Match_date]), Matches.Season, Query1.MatchID, Query1.DoublesGameID, Team.team_ID, Query1.Player, Query1.Side, Query1.Place;

Use that query as basis for further data manipulation. Example:
SELECT Query2.Yr, Query2.Season, Query2.MatchID, Query2.team_ID, Query2.Side, Count(Query2.Place) AS CountOfPlace, Count([Place])+IIf(Count([Place])>=3,IIf([Side]="Home",2,3),0) AS Points
FROM Query2
WHERE (((Query2.Place)="Win"))
GROUP BY Query2.Yr, Query2.Season, Query2.MatchID, Query2.team_ID, Query2.Side;

[Crazybrit]

OMG!!! Thanks again June7 i feel such a Lamer/newbie rest assured i have gathered loads from this forum in just a few days and i am sure there is more to come. I appreciate also your answer in my thread on the combo boxes in forms thanks again for sharing your time and superior knowledge
Kindest Regards Crazybrit