Update query: Difference between revisions
Jump to navigation
Jump to search
TOstryzniuk (talk | contribs) m (minior data processing) |
TOstryzniuk (talk | contribs) m (→Part 2: minor) |
||
Line 32: | Line 32: | ||
**Select and SAVE as : Query Type - '''APPEND''' | **Select and SAVE as : Query Type - '''APPEND''' | ||
***notice that the symbol beside the query you named will be different. | ***notice that the symbol beside the query you named will be different. | ||
***notice that the row that was labelled sort has | ***notice that the row that was labelled sort has now changed to: '''Update to'''. | ||
==Part 3== | ==Part 3== |
Revision as of 20:12, 2015 January 10
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 now 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 the whole field: 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.