Results 1 to 4 of 4
  1. #1
    visitnag is offline Novice
    Windows 11 Access 2021
    Join Date
    Dec 2022
    Posts
    17

    Update Query Not Updating the fields on a single run

    Hi,



    I have a table with 33 columns, in which 6 columns data will get updated depending on the values of other 27 columns.
    Even few column values are interdependent.

    Example : One of the Six columns value is input for second column of six columns.
    I have maintained the sequence also.

    I have created six user defined functions to update these 6 columns in sequence.

    When I run the Update Query all the columns are not getting updated at a time. I have to run the updatequery six times
    to get all the six columns to be updated.

    The user defined functions are having minimum 3 and max 5 parameters.

    I dont know what precautions to be taken while creating an update query.

    Please guide

    Thank you.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    The best advice is: Don't use calculated fields in Access tables without some compelling reason (like needing those values for form filtering). As rule, you calculate those values directly when and where they are needed.

  3. #3
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    As mentioned, it is normally not a good idea to store calculated fields.

    I think the problem here is the interdependence. If calculated field B depends on field A, you can't update them in one query. Updating B uses the value of A that A had before the query was started.
    So you will have to run a number of queries one after the other.
    Groeten,

    Peter

  4. #4
    visitnag is offline Novice
    Windows 11 Access 2021
    Join Date
    Dec 2022
    Posts
    17
    Quote Originally Posted by ArviLaanemets View Post
    The best advice is: Don't use calculated fields in Access tables without some compelling reason (like needing those values for form filtering). As rule, you calculate those values directly when and where they are needed.

    Thank you very much. It worked

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-18-2021, 05:13 AM
  2. Replies: 2
    Last Post: 11-27-2017, 08:59 PM
  3. updating single row per group in access query
    By sugat11 in forum Queries
    Replies: 6
    Last Post: 07-05-2016, 08:03 AM
  4. Replies: 3
    Last Post: 11-12-2012, 10:44 AM
  5. Replies: 1
    Last Post: 08-30-2011, 07:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums