Update query: Difference between revisions

(16 intermediate revisions by 2 users not shown)

Line 1:

~~Also referred~~ to ~~as update query~~

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 ~~bulk~~ update query ~~to a data field using Access~~ in ~~centralized front end (~~[[CFE]]).

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

Line 20:

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

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

==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 [[Media:Update query Part 2.JPG | Setting up ~~Append~~ Query Part 2-4]]

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

*Go to TAB at top of screen: Query Tools - Design

**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 row that was labelled sort has ~~not~~ changed to: '''Update to'''.

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

==Part 4-Ready to change data==

Press the red ! run button.

*query has done it job.

*query has done it's job.

Line 58:

Line 59:

[[Category: Data Processing]]

[[Category:Data Processing]]

[[Category:MS Access Tips and Techniques]]

[[Category:Query]]

@@ Line 1: / Line 1: @@
-Also referred to as update query
+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 bulk update query to a data field using Access in centralized front end ([[CFE]]).
+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 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
@@ Line 20: / Line 20: @@
 **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.
+***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 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.
 ==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 [[Media:Update query Part 2.JPG | Setting up Append Query Part 2-4]]
+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
+*Go to TAB at top of screen: Query Tools - Design
-**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 row that was labelled sort has not changed to: '''Update to'''.
+***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 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.
 ==Part 4-Ready to change data==
 Press the red ! run button.
-*query has done it job.
+*query has done it's job.
@@ Line 58: / Line 59: @@
-[[Category: Data Processing]]
+[[Category:Data Processing]]
+[[Category:MS Access Tips and Techniques]]
+[[Category:Query]]