Using SUM, MAX and MIN over multiple columns

[PaidtheUmpire]

I am currently using an Access database which has table like so...

NAME ROUND SCORE1 SCORE2 SCORE3

Bill 1 150 45 145
Bill 2 65 158 132
Fred 1 15 35 45

Is there a way of finding a persons SUM, MAX and MIN scores over all three score sessions, though an Access Query or SQL query? Or will i have to do it in the frontend?

Delphi I can't get enough of you.

 

[dhookom]

You should normalize your table structure so that your sql becomes easy. If you aren't able to normalize then create a union query:
SELECT [Player], Round, Score1 as Score, 1 as GameNum
FROM tblScores
UNION ALL
SELECT [Player], Round, Score2, 2
FROM tblScores
UNION ALL
SELECT [Player], Round, Score3, 3
FROM tblScores;

Then select Player, Max(Score) as MaxScore, Min(Score) as MinScore, Sum(Score) as SumScore
FROM quniYourUnionQuery
GROUP BY Player;



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]