Update query: Difference between revisions
Jump to navigation
Jump to search
TOstryzniuk (talk | contribs) m (→Part 2: minor) |
TOstryzniuk (talk | contribs) m (→Part 1: minor) |
||
Line 24: | Line 24: | ||
**In the SECOND column, change to field name to the following: '''NewValue:"DC" ''' | **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. | **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. | **SAVE with a intuitive name of what this update query will do. | ||
**Go back to DESIGN VIEW. Make any changes if needed. | **Go back to DESIGN VIEW. Make any changes if needed. | ||
Revision as of 16:46, 2015 January 9
Also referred to as 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.
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".
Steps broken down into 4 parts
Part 1
Set up a Selection Query. See Picture: Part 1 setting up update query
- 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_treat TWICE
- You should see R_dc_treat in two columns in the FIELD box.
- in the first column in the CRITERIA field, enter: "-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 intuitive name of what this update query will do.
- Go back to DESIGN VIEW. Make any changes if needed.
Part 2
Change this from a Selection Query to and Append Query that will actually prepare you to make change you set up after you run it. See Picture Setting up Append Query Part 2-4
- Go to TAB at top of screen: Query Tools - Design
- Select and SAVE as : Query Type - APPEND
- notice that the symbol beside the query you named will be different.
- notice that the row that was labelled sort has not changed to: Update to.
Part 3
- in the first column in the Update to field, type in: "DC"
- in the second column, first row called Field, remove NewValue: "DC"
- SAVE again as a APPEND - query type.
Part 4-Ready to change data
Press the red ! run button.
- query has done it job.