Still grappling with queries

[MikeB]

I'm writing queries galore. Perhaps there is something I don't "get."

In my chess database, if I want to know how a player has performed, I
wrote several queries.

One to calculate the number of games they won:

SELECT PlayerGames.PlayerNumber, Count(PlayerGames.Score) AS Won
FROM Tournaments INNER JOIN (PlayerGames INNER JOIN Matches ON
PlayerGames.MatchNumber = Matches.MatchNumber) ON
Tournaments.TournamentNumber = Matches.TournamentNumber
WHERE (((PlayerGames.Score)=1) AND ((PlayerGames.Color)="White" Or
(PlayerGames.Color)="Black"))
GROUP BY PlayerGames.PlayerNumber, Tournaments.TournamentNumber
HAVING (((Tournaments.TournamentNumber)=[Tournament?]));

Another to calculate the number of games played as White:

SELECT PlayerGames.PlayerNumber, Count(PlayerGames.PlayerNumber) AS
White
FROM Tournaments INNER JOIN (PlayerGames INNER JOIN Matches ON
PlayerGames.MatchNumber = Matches.MatchNumber) ON
Tournaments.TournamentNumber = Matches.TournamentNumber
WHERE (((Tournaments.TournamentNumber)=[Tournament?]) AND
((PlayerGames.Color)="White"))
GROUP BY PlayerGames.PlayerNumber;


Another for games lost, games drawn, games as black... you get the
picture.

Then I write a query to combine all of those into one table:

SELECT Players.PlayerNumber, Players.Firstname & " " &
Players.LastName AS Name, [GamesPlayed - White].White, [GamesPlayed -
Black].Black, [GamesPlayed - Bye].Byes AS Byes, [GamesPlayed -
Won].Won, [GamesPlayed - Lost].Lost, [GamesPlayed - Drawn].Draws AS
Draws, [GamesPlayed - Instruction].Instruction, [GamesPlayed -
Beginner].Beginner
FROM (((((((Players LEFT JOIN [GamesPlayed - White] ON
Players.PlayerNumber = [GamesPlayed - White].PlayerNumber) LEFT JOIN
[GamesPlayed - Black] ON Players.PlayerNumber = [GamesPlayed -
Black].PlayerNumber) LEFT JOIN [GamesPlayed - Bye] ON
Players.PlayerNumber = [GamesPlayed - Bye].PlayerNumber) LEFT JOIN
[GamesPlayed - Instruction] ON Players.PlayerNumber = [GamesPlayed -
Instruction].PlayerNumber) LEFT JOIN [GamesPlayed - Beginner] ON
Players.PlayerNumber = [GamesPlayed - Beginner].PlayerNumber) LEFT
JOIN [GamesPlayed - Drawn] ON Players.PlayerNumber = [GamesPlayed -
Drawn].PlayerNumber) LEFT JOIN [GamesPlayed - Lost] ON
Players.PlayerNumber = [GamesPlayed - Lost].PlayerNumber) LEFT JOIN
[GamesPlayed - Won] ON Players.PlayerNumber = [GamesPlayed -
Won].PlayerNumber;


Each and evey one of the above queries asks as a parameter for the
Tournament that the player played in.

Now I need to get lifetime performance of the player. I can't find a
way to make the parameter accept "All" or "*" tournaments, so it seems
I have to write each of the above queries again, and create another
summary query.

Now while it is not difficult to simply copy each of the queries,
modify it and save it, I'm just starting to have queries coming out of
the wazoo (pardon my expression). Is there a trick of the trade that
I'm missing or overlooking that will simplify my life?

Aside: Just looking at these queries, I noticed that some of them
still use the "HAVING" clause as opposed to the "WHERE" clause. I'll
get right on to that to fix that.

 

[Douglas J. Steele]

You don't need multiple queries to get your statistics.

Use IIf to handle the different conditions, and Sum the results:

SELECT PlayerGames.PlayerNumber,
Sum(IIf(PlayerGames.Score = 1, 1, 0)) AS Won,
Sum(IIf(PlayerGames.Color = "White", 1, 0)) AS White,
Sum(IIf(PlayerGames.Color = "Black", 1, 0)) AS Black
FROM Tournaments INNER JOIN (PlayerGames INNER JOIN Matches ON
PlayerGames.MatchNumber = Matches.MatchNumber) ON
Tournaments.TournamentNumber = Matches.TournamentNumber
WHERE ((Tournaments.TournamentNumber=[Tournament?])
OR ([Tournament?] IS NULL))
GROUP BY PlayerGames.PlayerNumber

The OR ([Tournament?] IS NULL)) part of the condition means that you can
simply hit Enter rather than entering a tournament number to get all
tournaments.

(Afraid I don't know what your conditions would be to calculate draws and
losses)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm writing queries galore. Perhaps there is something I don't "get."

In my chess database, if I want to know how a player has performed, I
wrote several queries.

One to calculate the number of games they won:

SELECT PlayerGames.PlayerNumber, Count(PlayerGames.Score) AS Won
FROM Tournaments INNER JOIN (PlayerGames INNER JOIN Matches ON
PlayerGames.MatchNumber = Matches.MatchNumber) ON
Tournaments.TournamentNumber = Matches.TournamentNumber
WHERE (((PlayerGames.Score)=1) AND ((PlayerGames.Color)="White" Or
(PlayerGames.Color)="Black"))
GROUP BY PlayerGames.PlayerNumber, Tournaments.TournamentNumber
HAVING (((Tournaments.TournamentNumber)=[Tournament?]));

Another to calculate the number of games played as White:

SELECT PlayerGames.PlayerNumber, Count(PlayerGames.PlayerNumber) AS
White
FROM Tournaments INNER JOIN (PlayerGames INNER JOIN Matches ON
PlayerGames.MatchNumber = Matches.MatchNumber) ON
Tournaments.TournamentNumber = Matches.TournamentNumber
WHERE (((Tournaments.TournamentNumber)=[Tournament?]) AND
((PlayerGames.Color)="White"))
GROUP BY PlayerGames.PlayerNumber;


Another for games lost, games drawn, games as black... you get the
picture.

Then I write a query to combine all of those into one table:

SELECT Players.PlayerNumber, Players.Firstname & " " &
Players.LastName AS Name, [GamesPlayed - White].White, [GamesPlayed -
Black].Black, [GamesPlayed - Bye].Byes AS Byes, [GamesPlayed -
Won].Won, [GamesPlayed - Lost].Lost, [GamesPlayed - Drawn].Draws AS
Draws, [GamesPlayed - Instruction].Instruction, [GamesPlayed -
Beginner].Beginner
FROM (((((((Players LEFT JOIN [GamesPlayed - White] ON
Players.PlayerNumber = [GamesPlayed - White].PlayerNumber) LEFT JOIN
[GamesPlayed - Black] ON Players.PlayerNumber = [GamesPlayed -
Black].PlayerNumber) LEFT JOIN [GamesPlayed - Bye] ON
Players.PlayerNumber = [GamesPlayed - Bye].PlayerNumber) LEFT JOIN
[GamesPlayed - Instruction] ON Players.PlayerNumber = [GamesPlayed -
Instruction].PlayerNumber) LEFT JOIN [GamesPlayed - Beginner] ON
Players.PlayerNumber = [GamesPlayed - Beginner].PlayerNumber) LEFT
JOIN [GamesPlayed - Drawn] ON Players.PlayerNumber = [GamesPlayed -
Drawn].PlayerNumber) LEFT JOIN [GamesPlayed - Lost] ON
Players.PlayerNumber = [GamesPlayed - Lost].PlayerNumber) LEFT JOIN
[GamesPlayed - Won] ON Players.PlayerNumber = [GamesPlayed -
Won].PlayerNumber;


Each and evey one of the above queries asks as a parameter for the
Tournament that the player played in.

Now I need to get lifetime performance of the player. I can't find a
way to make the parameter accept "All" or "*" tournaments, so it seems
I have to write each of the above queries again, and create another
summary query.

Now while it is not difficult to simply copy each of the queries,
modify it and save it, I'm just starting to have queries coming out of
the wazoo (pardon my expression). Is there a trick of the trade that
I'm missing or overlooking that will simplify my life?

Aside: Just looking at these queries, I noticed that some of them
still use the "HAVING" clause as opposed to the "WHERE" clause. I'll
get right on to that to fix that.

 

[KARL DEWEY]

Try using these for the HAVING & WHERE --
HAVING (((Tournaments.TournamentNumber) Like IIF([Tournament? or press
ENTER] Is Null, "*", [Tournament?])))
WHERE (((Tournaments.TournamentNumber) Like IIF([Tournament? or press ENTER]
Is Null, "*", [Tournament?]))


--
KARL DEWEY
Build a little - Test a little

I'm writing queries galore. Perhaps there is something I don't "get."

In my chess database, if I want to know how a player has performed, I
wrote several queries.

One to calculate the number of games they won:

SELECT PlayerGames.PlayerNumber, Count(PlayerGames.Score) AS Won
FROM Tournaments INNER JOIN (PlayerGames INNER JOIN Matches ON
PlayerGames.MatchNumber = Matches.MatchNumber) ON
Tournaments.TournamentNumber = Matches.TournamentNumber
WHERE (((PlayerGames.Score)=1) AND ((PlayerGames.Color)="White" Or
(PlayerGames.Color)="Black"))
GROUP BY PlayerGames.PlayerNumber, Tournaments.TournamentNumber
HAVING (((Tournaments.TournamentNumber)=[Tournament?]));

Another to calculate the number of games played as White:

SELECT PlayerGames.PlayerNumber, Count(PlayerGames.PlayerNumber) AS
White
FROM Tournaments INNER JOIN (PlayerGames INNER JOIN Matches ON
PlayerGames.MatchNumber = Matches.MatchNumber) ON
Tournaments.TournamentNumber = Matches.TournamentNumber
WHERE (((Tournaments.TournamentNumber)=[Tournament?]) AND
((PlayerGames.Color)="White"))
GROUP BY PlayerGames.PlayerNumber;


Another for games lost, games drawn, games as black... you get the
picture.

Then I write a query to combine all of those into one table:

SELECT Players.PlayerNumber, Players.Firstname & " " &
Players.LastName AS Name, [GamesPlayed - White].White, [GamesPlayed -
Black].Black, [GamesPlayed - Bye].Byes AS Byes, [GamesPlayed -
Won].Won, [GamesPlayed - Lost].Lost, [GamesPlayed - Drawn].Draws AS
Draws, [GamesPlayed - Instruction].Instruction, [GamesPlayed -
Beginner].Beginner
FROM (((((((Players LEFT JOIN [GamesPlayed - White] ON
Players.PlayerNumber = [GamesPlayed - White].PlayerNumber) LEFT JOIN
[GamesPlayed - Black] ON Players.PlayerNumber = [GamesPlayed -
Black].PlayerNumber) LEFT JOIN [GamesPlayed - Bye] ON
Players.PlayerNumber = [GamesPlayed - Bye].PlayerNumber) LEFT JOIN
[GamesPlayed - Instruction] ON Players.PlayerNumber = [GamesPlayed -
Instruction].PlayerNumber) LEFT JOIN [GamesPlayed - Beginner] ON
Players.PlayerNumber = [GamesPlayed - Beginner].PlayerNumber) LEFT
JOIN [GamesPlayed - Drawn] ON Players.PlayerNumber = [GamesPlayed -
Drawn].PlayerNumber) LEFT JOIN [GamesPlayed - Lost] ON
Players.PlayerNumber = [GamesPlayed - Lost].PlayerNumber) LEFT JOIN
[GamesPlayed - Won] ON Players.PlayerNumber = [GamesPlayed -
Won].PlayerNumber;


Each and evey one of the above queries asks as a parameter for the
Tournament that the player played in.

Now I need to get lifetime performance of the player. I can't find a
way to make the parameter accept "All" or "*" tournaments, so it seems
I have to write each of the above queries again, and create another
summary query.

Now while it is not difficult to simply copy each of the queries,
modify it and save it, I'm just starting to have queries coming out of
the wazoo (pardon my expression). Is there a trick of the trade that
I'm missing or overlooking that will simplify my life?

Aside: Just looking at these queries, I noticed that some of them
still use the "HAVING" clause as opposed to the "WHERE" clause. I'll
get right on to that to fix that.

 

[MikeB]

Try using these for the HAVING & WHERE --
HAVING (((Tournaments.TournamentNumber) Like IIF([Tournament? or press
ENTER] Is Null, "*", [Tournament?])))
WHERE (((Tournaments.TournamentNumber) Like IIF([Tournament? or press ENTER]
Is Null, "*", [Tournament?]))

Karl, thanks so much. That worked, after a fashion. First time I
tried, I cut/pasted your Where clause into a query of mine. It worked,
I wasn't sure and backed it out to compare the results. Then when I
tried again, I had a syntax error with the brackets which I had to
fix.

Anyway, the way you wrote it I got two prompts if I entered a null
value.

I rewrote it as follows and it seems to work just as well.

WHERE (((Tournaments.TournamentNumber) Like [Tournament?]))

But thanks, it makes it much easier.

 

[MikeB]

You don't need multiple queries to get your statistics.

Use IIf to handle the different conditions, and Sum the results:

SELECT PlayerGames.PlayerNumber,
Sum(IIf(PlayerGames.Score = 1, 1, 0)) AS Won,
Sum(IIf(PlayerGames.Color = "White", 1, 0)) AS White,
Sum(IIf(PlayerGames.Color = "Black", 1, 0)) AS Black
FROM Tournaments INNER JOIN (PlayerGames INNER JOIN Matches ON
PlayerGames.MatchNumber = Matches.MatchNumber) ON
Tournaments.TournamentNumber = Matches.TournamentNumber
WHERE ((Tournaments.TournamentNumber=[Tournament?])
OR ([Tournament?] IS NULL))
GROUP BY PlayerGames.PlayerNumber

The OR ([Tournament?] IS NULL)) part of the condition means that you can
simply hit Enter rather than entering a tournament number to get all
tournaments.

(Afraid I don't know what your conditions would be to calculate draws and
losses)

Doug you rock!

The condition for a loss is 0 points and for a draw is 0.5 points.
Easy enough to add in.

I knew I wasn't doing it right, but I didn't know how to fix it.
Thanks again to you and Karl.

 

[Douglas J. Steele]

While it may not matter in this particular case, I think it's better not to
use Like * when nothing's input. That's because you won't get those records
that don't have any value associated with them.

In other words, I think it's better to use

WHERE ((Tournaments.TournamentNumber = [Tournament?]) OR ([Tournament?] IS
NULL))

For what it's worth, the reason Karl's solution prompted you twice was
because the two prompts were different. His solution had [Tournament? or
press ENTER] in one place, but only [Tournament?] in the other. They must be
identical.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Try using these for the HAVING & WHERE --
HAVING (((Tournaments.TournamentNumber) Like IIF([Tournament? or press
ENTER] Is Null, "*", [Tournament?])))
WHERE (((Tournaments.TournamentNumber) Like IIF([Tournament? or press
ENTER]
Is Null, "*", [Tournament?]))

Karl, thanks so much. That worked, after a fashion. First time I
tried, I cut/pasted your Where clause into a query of mine. It worked,
I wasn't sure and backed it out to compare the results. Then when I
tried again, I had a syntax error with the brackets which I had to
fix.

Anyway, the way you wrote it I got two prompts if I entered a null
value.

I rewrote it as follows and it seems to work just as well.

WHERE (((Tournaments.TournamentNumber) Like [Tournament?]))

But thanks, it makes it much easier.