Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Pune, Maharashtra, India
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Partial Update of Fields in a record

    I have a database containing say ten fields. Four out of these are filled with data input by users. I want to do some calculations with user supplied data and put in calculated values in other six fields. Is it possible to use Append or Update queries for this purpose.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Partial Update of Fields in a record

    You certainly could use an update query to update your table with calculated data. However, a better method would be to have a query display the calculations with the data. Your reports could then run off this query and your table size is greatly reduced. Plus anytime you view your data using the query with calculated fields the calculations are up-to-date and accurate. This may not be the case when you have to run an update query in order to change the calculated amounts in your table.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Partial Update of Fields in a record

    It is considered poor design to put calculated values into tables. The exception to this is so-called
    <hr>snapshots<hr>
    , which are records that contain things like calculated balance as of a particular date. These are used frequently in accounting, sales and inventory applications. However, these records are not updated. Instead a new record is added periodically with an updated balance.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Partial Update of Fields in a record

    The others are correct in that it is almost never desirable to have redundant information in your tables. However, I have broken that rule a few times when it was desirable to have some sort of concatenation of fields stored as a separate field, e.g. as a key (and yes, I know about autonumbers as well as combined primary keys - explaining myself would take a long message[img]/w3timages/icons/kiss.gif[/img]).

    Anyway, you would use update queries if you wanted to set those fields after the fact. If this is something you are presently setting up, I would suggest using code in your forms (in the After Update event code of the relevant fields that the user is entering) to store the "calculated" values.

Posting Permissions

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