Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Get Min values where field value <> -1

Author  Topic  Rinso Starting Member 1 Post Posted - 2012-12-05 : 12:18:53 I have a query where I wish to select the minimum score for each hole for a set of golf scores over a certain date range. Scores are stored as integers BUT I have a twist - where if a hole is 'wiped' during the round, the score is set to -1. I need to get the min of the non(-1) values and the count of the number of rounds played by each player over that time period. I tied this, but found it omits rows that have a -1 holescore value and it does not include a COUNT function yet; SELECT Players.FirstName, Players.LastName, Players.Gender, Players.Grade, Scores.DatePlayed, Min(Scores.ScoreHole1) AS MinOfScoreHole1, Min(Scores.ScoreHole2) AS MinOfScoreHole2, Min(Scores.ScoreHole3) AS MinOfScoreHole3, Min(Scores.ScoreHole4) AS MinOfScoreHole4, Min(Scores.ScoreHole5) AS MinOfScoreHole5, Min(Scores.ScoreHole6) AS MinOfScoreHole6, Min(Scores.ScoreHole7) AS MinOfScoreHole7, Min(Scores.ScoreHole8) AS MinOfScoreHole8, Min(Scores.ScoreHole9) AS MinOfScoreHole9, Min(Scores.ScoreHole10) AS MinOfScoreHole10, Min(Scores.ScoreHole11) AS MinOfScoreHole11, Min(Scores.ScoreHole12) AS MinOfScoreHole12, Min(Scores.ScoreHole13) AS MinOfScoreHole13, Min(Scores.ScoreHole14) AS MinOfScoreHole14, Min(Scores.ScoreHole15) AS MinOfScoreHole15, Min(Scores.ScoreHole16) AS MinOfScoreHole16, Min(Scores.ScoreHole17) AS MinOfScoreHole17, Min(Scores.ScoreHole18) AS MinOfScoreHole18 FROM Players INNER JOIN Scores ON Players.PlayerId = Scores.PlayerId GROUP BY Players.FirstName, Players.LastName, Players.Gender, Players.Grade, Scores.DatePlayed, Scores.DatePlayed HAVING (((Players.Gender)="F") AND ((Scores.DatePlayed)>=#11/26/2012# And (Scores.DatePlayed)<=#3/12/2012#) AND ((Min(Scores.ScoreHole1))<>-1) AND ((Min(Scores.ScoreHole2))<>-1) AND ((Min(Scores.ScoreHole3))<>-1) AND ((Min(Scores.ScoreHole4))<>-1) AND ((Min(Scores.ScoreHole5))<>-1) AND ((Min(Scores.ScoreHole6))<>-1) AND ((Min(Scores.ScoreHole7))<>-1) AND ((Min(Scores.ScoreHole8))<>-1) AND ((Min(Scores.ScoreHole9))<>-1) AND ((Min(Scores.ScoreHole10))<>-1) AND ((Min(Scores.ScoreHole11))<>-1) AND ((Min(Scores.ScoreHole12))<>-1) AND ((Min(Scores.ScoreHole13))<>-1) AND ((Min(Scores.ScoreHole14))<>-1) AND ((Min(Scores.ScoreHole15))<>-1) AND ((Min(Scores.ScoreHole16))<>-1) AND ((Min(Scores.ScoreHole17))<>-1) AND ((Min(Scores.ScoreHole18))<>-1)) ORDER BY Players.LastName; All assistance greatly appreciated.