Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    1

    Unanswered: Update Only Values in Table that Meet Certain Condition

    In MS Access, I have a table with various fields (AlgScore, EngScore, ScienceScore, etc.) and each field contains values 1-99. I'd like to create a query that would set/update all values above 90 to null. I understand I can create an update query one field at a time. But I cannot figure out how to update ALL values greater than 90 to null in the entire table at one time.

    Any help would be appreciated.

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    WHERE [YourField] > 90
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Try the For Each structure. Consult the Help for information.

    However, you can't set a numeric field to null, to my knowledge.

    Sam

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Woah Sam, what's that suggestion all about?
    Why for each when you can just run a single update statement?

    And you can set a numeric field to NULL
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by glry2him
    In MS Access, I have a table with various fields (AlgScore, EngScore, ScienceScore, etc.) and each field contains values 1-99. I'd like to create a query that would set/update all values above 90 to null. I understand I can create an update query one field at a time. But I cannot figure out how to update ALL values greater than 90 to null in the entire table at one time.

    Any help would be appreciated.
    smells like iffy design to me.....
    a table with mulitple columns all realting to a score for a specific element....

    I suspect if you continue with your design you will need to do a different update for each score

    eg
    update mytable set EngScore=NULL where EngScore>90
    update mytable set AlgScore=NULL where AlgScore>90
    update mytable set ScienceScore=NULL where ScienceScore>90
    etc....
    if the design was normalised
    update mytable set Score=NULL where Score>90
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're right, georgev, you can set anumeric field to null.

    As another plug for normalizing; what're you going to do, glry2him, if/when your course of study changes? Are you going to add/subtract fields each time? Why not simply change your structure to include 2 fields called CourseOfStudy and CourseScore, or some such? Then you can simply say

    ...SET CourseScore = Null WHERE CourseScore > 90

    Hope this helps,
    Sam

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Definately retink the table structure. If you do keep the structure as is, you can use an IIF statement in the update query for each field.

    iif(AlgScore > 90,null,AlgScore)
    iif(EngScore > 90,null,EngScore)

Posting Permissions

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