Update query: Difference between revisions
Jump to navigation
Jump to search
TOstryzniuk (talk | contribs) m (minor) |
Ttenbergen (talk | contribs) (Update query vs select query, question answered.) |
||
(30 intermediate revisions by one other user not shown) | |||
Line 1: | Line 1: | ||
This article explains how to write a | Update queries make bulk changes to data based on a set of criteria. (Unlike Select queries that just show the data.) | ||
This article explains how to write a update query, e.g. in [[CFE]]. | |||
The steps below use one example to demonstrate how to replace a specific item in a data field with another. | The steps below use one example to demonstrate how to replace a specific item in a data field with another. | ||
Example for demonstration of steps 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". | *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== | ==Part 1== | ||
Set up | Set up a '''Selection Query.''' See Picture: [[Media:Update Query Part 1.JPG | 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. | *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]] | *Open [[CFE]] | ||
Line 17: | Line 19: | ||
**Close the table box | **Close the table box | ||
**Scroll down and find '''R_dc_treat'''. | **Scroll down and find '''R_dc_treat'''. | ||
**Double click on '''R_dc_treat'''TWICE | **Double click on '''R_dc_treat''' TWICE | ||
**You should see R_dc_treat in two columns in the FIELD box. | ***You should see R_dc_treat in two columns in the FIELD box. | ||
**In the SECOND column, change to field name to the following: '''NewValue:'' | **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 '''Update Query''' that will actually prepare you to make change you set up after you run it. See Picture [[Media:Update query Part 2.JPG | Setting up Update 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 entry from 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's job. | |||
Line 37: | Line 60: | ||
[[Category: Data Processing]] | [[Category: Data Processing]] | ||
[[Category: MS Access Tips and Techniques]] | |||
[[Category: Query]] |
Latest revision as of 15:42, 2017 September 20
Update queries make bulk changes to data based on a set of criteria. (Unlike Select queries that just show the data.)
This article explains how to write a update query, e.g. in CFE.
The steps below use one example to demonstrate how to replace a specific item in a data field with another.
Example for demonstration of steps 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 Update Query that will actually prepare you to make change you set up after you run it. See Picture Setting up Update 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 entry from 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's job.