Update query

This article explains how to write a bulk update query to a data field using Access in centralized front end (CFE).

The steps below use one example to demonstrate how to replace a specific item in a data field with another.

The example below:

• in the "R_dc_treat" field in "L_log", there are erroneous values of "-1" however, all of these values must be replace with the value "DC".

There are two parts in the process:

Part 1

Set up the selection query first

• this allows you to look if the update you're are planning is going to do what you need it to do before you actually proceed to part 2.
• Open CFE
• in the tool bar select the following:
  • CREATE
  • Select: Query Design. A list of tables will appear
  • Select: L_Log
  • Close the table box
  • Scroll down and find R_dc_treat.
  • Double click on R_dc_treatTWICE
  • You should see R_dc_treat in two columns in the FIELD box.
  • in the first column in the CRITERIA field type: "-1"
  • In the SECOND column, change to field name to the following: NewValue:DC
  • Select DATASHEET VIEW. You will see in your first column what the value currently is and in the second column, what the value will be replaced with.
  • SAVE with a name of what this update query will do.
  • Go back to DESIGN VIEW. Make any changes if needed.

Part 2

You will now do the final steps to change this from a selection query to and Append query that will actually make the change you set up.

• • Go to TAB: Query Tools - Design
  • Select and SAVE as : Query Type - APPEND
    • you will notice that the symbol beside the query you named will be different.
  • you will also notice that the row that was labelled sort has not changed to: Update TO.

Final steps

in the first column in the Update to field, type in: "DC"

• in the second column, first row, remove from the field NewValue: "DC"
• Save again as a APPEND - query type.

Ready change data

Press the red ! run button.

• query has done it job.