Help with a query

[youretoast]

Here is my problem and I suspect this is a simple question for the
experts.

My query attempts to get the names of players that played in a game
during the past x minutes and then the average scores of that player
for ALL games played by that player.

The query I have right now looks something like this (its from an
Access query but you get the point):

SELECT players.name, count(game.gameno) as gamesplayed,
Avg(game_players.score) AS avgscore
FROM players INNER JOIN (game INNER JOIN game_players ON game.gameno =
game_players.gameno) ON players.playerno = game_players.playerno
WHERE (((game.date_played) Between DateAdd('n',-100000,Now()) And
Now()))
Group by players.name

As you see the Avg function will only return the average scores in the
games played that meet the "WHERE" condition. How can I get the
average score to be the Average for ALL scores for that player?

Thanks in advance.

 

[John Spencer]

Try the following

SELECT players.name
, count(game.gameno) as gamesplayed
, Avg(game_players.score) AS avgscore
FROM players INNER JOIN (game INNER JOIN game_players
ON game.gameno = game_players.gameno)
ON players.playerno = game_players.playerno
WHERE Players.PlayerNo in (
SELECT GP.PlayerNo
FROM game_Players as GP INNER JOIN Game as G
ON G.date_played Between DateAdd('n',-100000,Now())
And Now() )
Group by players.name

Basically, you want to identify the PlayerNo for all the players that played
during the specified time frame and then get the count of the games ever
played and the avg score for all those games.

If you need the number of games played during the specified period, then
post back and perhaps someone will have a suggestion for you.