If you are looking for the Top Score by individual/member and what Software was that score achieved in, then this may be a solution.
qryTopScoreByMember
Code:
SELECT [Member Game].[Member Reference]
,[Member Game].[Software Reference]
,[Member Game].Score
FROM [Member Game]
WHERE [Member Game].[Score] IN (
SELECT TOP 1 [Dupe].Score
FROM [Member Game] AS Dupe
WHERE Dupe.[Member Reference] = [Member Game].[Member Reference]
ORDER BY Dupe.[Member Reference]
,Dupe.score DESC
)
ORDER BY [Member Game].[Member Reference]
,[Member Game].[Software Reference]
,[Member Game].Score;
If the question is to report the highest score for each Software, then the following may be a solution.
Code:
SELECT [Member Game].[Software Reference]
,Max([Member Game].Score) AS MaxOfScore
FROM [Member Game]
GROUP BY [Member Game].[Software Reference];
If you need the HiScore for each Software and who scored that mark, then this may be helpful.
qryHiScoreBySoftwareShowMember
Code:
SELECT [Member Game].[Software Reference]
,[Member Game].score AS HiScore
,[Member Game].[Member Reference] AS ScoredByMember
FROM [Member Game]
WHERE [member game].score = (
SELECT Max([dup].[Score])
FROM [Member Game] AS dup
WHERE dup.[software reference] = [Member Game].[Software Reference]
)
ORDER BY [Member Game].[software reference];