Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: Problem with an IIF statement

    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, )));


    Thanks
    Dockerspud

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    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.

    Dockerspud

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What I was trying to tease from you was information to see if you are making a mistake here - violating Third Normal Form:
    The Relational Data Model, Normalisation and effective Database Design

    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    pootle flump

    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.


    Dockerspud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •