sum n scores

[Pete]

I have a query which a previous visit to this forum gave me, it almost does
what I require but not quite. Previous attempts to complete this have
proved unsuccessful.

I have a table, which grows each week:

Player Score
Player 1 200
Player 2 300
Player 3 200
Player 4 200
Player 1 200
Player 2 300
Player 3 600
Player 4 100
Player 1 200
Player 2 500
Player 3 100
Player 4 100
Player 1 200
Player 2 300
Player 3 300
Player 4 100
Player 1 200
Player 2 500
Player 3 200
Player 4 200


what I require is a query that produces the sum of a players top n number of
scores, not all his scores just say the top 3 three. The problem with the
query below is it fails to break ties. Using it player 1's total is always
1,000 whether n is set to 1,2,3,4 or five.

SELECT [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName] &
" (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID =
T.PlayerID AND T1.Score >= T.Score))<=[NoOfGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")", tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC;

The problem has been isolated as having no primary key on the virtual table
with wich to uniquely order the scores by. I do not know how to solve this
issue, hopefully with this verbose explanation of the problem somebody will
take the time to solve this issue.

I cannot get on-line everyday to follwoing things up and it seems if I start
a trial and then cannot get on-line the help trail goes cold. Please assist
me to solve this issue.

 

[Guest]

You are quite right that the problem is due to the lack of a unique key
column, but you can solve that simply by adding an autonumber column, YourID
in the example below, to your table of scores, called YourTable in the
example below. You can then use a subquery which returns the YourID values
for the TOP 3 scores per player. By including the YourID column in the
subquery's ORDER BY clause you can eliminate any redundant ties. The TOP
option does not allow you to use a parameter, however, so you'd either have
to amend the querydef objects SQL property in code before running it if you
wanted to vary the Top n scores on the fly or build the SQL statement afresh
in code each time and assign it to the RecordSource property of a form or
report for instance:

SELECT Player, SUM(Score) As ScoreSum
FROM YourTable AS T1
WHERE YourID IN
(SELECT TOP 3 YourID
FROM YourTable As T2
WHERE T2.Player = T1.Player
ORDER BY Score DESC,YourID)
GROUP BY Player;

You can of course join a Players table to YourTable in the outer query on
the Player column (PlayerID in your real tables by the look of it) provided
you include any columns returned from that table in the GROUP BY clause.
Alternatively you could keep the above as a self-contained query and join
other tables to it in a second query.

Ken Sheridan
Stafford, England

I have a query which a previous visit to this forum gave me, it almost does
what I require but not quite. Previous attempts to complete this have
proved unsuccessful.

I have a table, which grows each week:

Player Score
Player 1 200
Player 2 300
Player 3 200
Player 4 200
Player 1 200
Player 2 300
Player 3 600
Player 4 100
Player 1 200
Player 2 500
Player 3 100
Player 4 100
Player 1 200
Player 2 300
Player 3 300
Player 4 100
Player 1 200
Player 2 500
Player 3 200
Player 4 200


what I require is a query that produces the sum of a players top n number of
scores, not all his scores just say the top 3 three. The problem with the
query below is it fails to break ties. Using it player 1's total is always
1,000 whether n is set to 1,2,3,4 or five.

SELECT [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName] &
" (" & [tPlayers]![TeamName] & ")" AS FullName, Sum(T.Score) AS Ranking,
tPlayers.PlayerID
FROM LeagueRoundsPlayed, ___PlayerRankings AS T INNER JOIN tPlayers ON
T.PlayerID = tPlayers.PlayerID
WHERE ((((SELECT COUNT(*) FROM ___PlayerRankings T1 WHERE T1.PlayerID =
T.PlayerID AND T1.Score >= T.Score))<=[NoOfGames]))
GROUP BY [tPlayers]![PlayerFirstName] & " " & [tPlayers]![PlayerSecondName]
& " (" & [tPlayers]![TeamName] & ")", tPlayers.PlayerID, T.PlayerID
ORDER BY Sum(T.Score) DESC;

The problem has been isolated as having no primary key on the virtual table
with wich to uniquely order the scores by. I do not know how to solve this
issue, hopefully with this verbose explanation of the problem somebody will
take the time to solve this issue.

I cannot get on-line everyday to follwoing things up and it seems if I start
a trial and then cannot get on-line the help trail goes cold. Please assist
me to solve this issue.