Find the lowest value (Min ?)

M

Mike

I have a data base that tracks the scores for a eucher tournament. The scores
from 10 different games are loaded and each field is called "Game Score 1",
"Game Score 2" etc. Each player has his/her own record with each game score
recorded. I want to total all 10 games and subtract the score from the lowest
game from the total. How???

Example Game Score 1 = 2
Game Score 2 = 10
Game Score 3 = 7
Total = 19
- 2 (score from game 1)
Recorded score = 17

Any help is appreciated. If I need to seperate into speperate tables that is
fine. I'm Lost!

Thanks
 
J

Josh Grameson

You can use DMin() to get the lowest score and then substract that from the
total
 
T

TedMi

Relational databases do not support searching across columns with SQL
queries. You need to normalize your data, with each game's score in a
separate row, each row identified by player ID and Game no. Let's say the
scores are kept in a column named GameScore. Then your result would be given
by:

SELECT SUM(GameScore) - MIN(GameScore) AS Result FROM [NameOfTable] WHERE
[PlayerID] = someID
 
Top