Find the lowest value (Min ?)

[Guest]

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

 

[Josh Grameson]

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

 

[Guest]

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

 

[Guest]

I've looked for the DMin() in help, but can't find it. How do I do it?