Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update query (2003 (SP1))

    I'm doing a reporting system for data on an AS/400 server. Due to the network / server performance, I'm importing relevant data into local talbes.

    To ensure the records are reasonably current, I've been considering using an update query to ensure the records that do change show those changes (at some point anyway).

    As this is the first time for me to use an update query, I'm finding the available help files a bit confusing.

    OK I understand how to change data according to criteria, but if the [status] in one relavant record is altered, will the query change the value of just that record. Also, if a previously empty field (within a record) now has data, will it be changed for (again) just that record?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (2003 (SP1))

    If you have an update query based on an imported table and a linked table, joined on the appropriate field(s), with criteria that FieldA in the linked table is not equal to FieldA in the imported table, the update query will only act on those record for which the value of FieldA has changed from one value to another. It will not act on records whose value in FieldA has not changed.
    You cannot compare Null to a "real" value, so the above criteria will not pick up a change from null to a "real" value, or from a "real" value to 0. You'll have to handle those separately. See screenshot.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2003 (SP1))

    Can/does this work if there are a few changed fields?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (2003 (SP1))

    Yes, but it quickly becomes tedious. You'd add those three criteria for each field you want to monitor, each set on its own three lines. So if you want to monitor changes in 4 fields, you'd have 4*3 = 12 criteria lines.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2003 (SP1))

    Would it be quicker/simpler to just run a delete query, followed by an append query?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (2003 (SP1))

    Do you have relationships with enforced referential integrity involving the imported table? That could cause problems. Otherwise, what you propose should work. Overwriting the importer table would be even simpler.

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (2003 (SP1))

    Oh, what's the best method for over-writing? Make table query?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (2003 (SP1))

    Yes. When run interactively, you'll be prompted whether you want to overwrite the existing table. If you use code, you can suppress the warning (DoCmd.SetWarnings False before, and DoCmd.SetWarnings True after executing the query).

Posting Permissions

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