Update query: Difference between revisions

(31 intermediate revisions by 2 users not shown)

Line 1:

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

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 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".

Steps broken down into 4 parts

~~There are two parts in the process:~~

==Part 1==

Set up ~~the selection~~ query ~~first~~

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.

*Open [[CFE]]

Line 17:

Line 19:

**Close the table box

**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:''DC'' '''

**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 36:

Line 59:

[[Category: Data Processing]]

[[Category:Data Processing]]

[[Category:MS Access Tips and Techniques]]

[[Category:Query]]

@@ Line 1: / Line 1: @@
-This article explains how to write a bulk update query to a data field using Access in centralized front end ([[CFE]]).
+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 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".
+Steps broken down into 4 parts
-There are two parts in the process:
 ==Part 1==
-Set up the selection query first
+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.
 *Open [[CFE]]
@@ Line 17: / Line 19: @@
 **Close the table box
 **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:''DC'' '''
+**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 36: / Line 59: @@
-[[Category: Data Processing]]
+[[Category:Data Processing]]
+[[Category:MS Access Tips and Techniques]]
+[[Category:Query]]