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.) |
||
(26 intermediate revisions by one other user not shown) | |||
Line 1: | Line 1: | ||
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 | 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 below: | 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 | Steps broken down into 4 parts | ||
==Part 1== | ==Part 1== | ||
Set up a | 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 19: | 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 first column in the CRITERIA field | **in the first column in the CRITERIA field, enter: "-1" | ||
**In the SECOND column, change to field name to the following: '''NewValue: | **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. | ||
==Part 2== | ==Part 2== | ||
Change this from a Selection Query to and | 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 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== | ||
*in the first column in the '''Update to''' field, type in: "DC" | *in the first column in the '''Update to''' field, type in: "DC" | ||
*in the second column, first row called Field, remove NewValue: "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. | *SAVE again as a APPEND - query type. | ||
==Part 4-Ready to change data== | ==Part 4-Ready to change data== | ||
Press the red ! run button. | Press the red ! run button. | ||
*query has done it job. | *query has done it's job. | ||
Line 58: | 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.