I have a Table that I record all Golf Scores for each of the 18 holes,
I want to update all the records that are null, under certain conditions, ie players handicap. Different handicap results in a different value for the record. As there are values in most of the records already I want those to stay there, only the nulls to update.
I tried the following but it didn't achieve the result.
UPDATE IndexModified INNER JOIN Members ON IndexModified.GolfLinkNumber=Members.GolfLinkNumbe r SET IndexModified.no4
=IIf([IndexModified.no1] Is Null And [Members.ExactHandicap] Between 1 And 15.4,5,IIf([IndexModified.no1] Is Null And [Members.ExactHandicap] Between 15.5 And 32.4,6,IIf([IndexModified.no1] Is Null And [Members.ExactHandicap]>32.5,7, )));
Quick question - don't handicaps change? If Poots the golfer has a handicap of 3 one week, and then 2 the next - would you need to change the calculations you have just made to the table, or would you just use handicap of 2 for future calculations?
Handicaps do change. Calculting which numbers update the records to will depend on the Holes Index. I will need to create 18 update queries to cover all scenarios. The players handicap is recorded at the time of the score. But for now I want to do a mass update of 10000 records.
Once these records are updated I'll create a new table then I'll append to it as the need arises. As I append it i'll update the new records using the players new handicaps.
I think you are - if you have the handicap at the time of the score then you can calculate this when you need it - you should not store it. Crudely put, Third Normal Form states that you should not store anything that you can calculate\ derive from the values in other columns.
I can't calculate the handicap using the scores that I am entering.
The handicap is stored in the players table not the scores table.
In some games of golf the player doesn't complete the hole, so they leave a blank on thier score card. I do not enter anything in the database, but now i want put a score in there so I can work out an average for all holes.