update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi could someone help on this. Here are the details.
I have a table with multiple columns. I want to creat a new column in the
same table and update that column depending on the values from the existing
column. For example if columnA (existing Column) has value of 0 or 999
newcolumn should have value of 12345, same way if the columnA has values
between 300-600 range the new column should have value of 78900 and so on.
Like this I need to define 3 to 4 conditions on the existing column to get
the new column values. I hope I explained it right. please help
 
I would use a cross reference table (XRef) that has three fields but would
use it only for query output and not record it as it probably will change in
the future.
High -
Low -
xRef -
In your query add an output field (the same could be used as update
information) like this --
CrossRef: IIF([ColumnA] Between [XRef].[High] And [XRef].[Low],
[XRef].[xref], "Error")
You might use zero instead of "Error" above.
 
Hi could you be more specific. As I explained in my example below, I want all
the columns in one table. My result table should look like this
columnA ColumnB ColumnC NewColumn
0 xxxxxxx xxxxxxxx 12345
999 xxxxxxx xxxxxxxx 12345
100 xxxxxxx xxxxxxxx 45678
300 xxxxxxx xxxxxxxx 78900
550 xxxxxxx xxxxxxxx 78900

if the range is inbetween 300-500 valued should be 78900
KARL DEWEY said:
I would use a cross reference table (XRef) that has three fields but would
use it only for query output and not record it as it probably will change in
the future.
High -
Low -
xRef -
In your query add an output field (the same could be used as update
information) like this --
CrossRef: IIF([ColumnA] Between [XRef].[High] And [XRef].[Low],
[XRef].[xref], "Error")
You might use zero instead of "Error" above.

sr25 said:
Hi could someone help on this. Here are the details.
I have a table with multiple columns. I want to creat a new column in the
same table and update that column depending on the values from the existing
column. For example if columnA (existing Column) has value of 0 or 999
newcolumn should have value of 12345, same way if the columnA has values
between 300-600 range the new column should have value of 78900 and so on.
Like this I need to define 3 to 4 conditions on the existing column to get
the new column values. I hope I explained it right. please help
Click to expand...
Click to expand...
 
Open the table in design view and add the new column - save.

Create a select query using your table. Open in design view. Add the cross
reference table but do not join. Change the query to an update query - the
icon looks like two tables and a down arrow to select query type.

In the Update To row insert --
IIF([ColumnA] Between [XRef].[High] And [XRef].[Low], [XRef].[xref], "Error")

In the criteria for columnA put --
Between [XRef].[High] And [XRef].[Low]

sr25 said:
Hi could you be more specific. As I explained in my example below, I want all
the columns in one table. My result table should look like this
columnA ColumnB ColumnC NewColumn
0 xxxxxxx xxxxxxxx 12345
999 xxxxxxx xxxxxxxx 12345
100 xxxxxxx xxxxxxxx 45678
300 xxxxxxx xxxxxxxx 78900
550 xxxxxxx xxxxxxxx 78900

if the range is inbetween 300-500 valued should be 78900
KARL DEWEY said:
I would use a cross reference table (XRef) that has three fields but would
use it only for query output and not record it as it probably will change in
the future.
High -
Low -
xRef -
In your query add an output field (the same could be used as update
information) like this --
CrossRef: IIF([ColumnA] Between [XRef].[High] And [XRef].[Low],
[XRef].[xref], "Error")
You might use zero instead of "Error" above.

sr25 said:
Hi could someone help on this. Here are the details.
I have a table with multiple columns. I want to creat a new column in the
same table and update that column depending on the values from the existing
column. For example if columnA (existing Column) has value of 0 or 999
newcolumn should have value of 12345, same way if the columnA has values
between 300-600 range the new column should have value of 78900 and so on.
Like this I need to define 3 to 4 conditions on the existing column to get
the new column values. I hope I explained it right. please help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top