A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Update field based on current value



 
 
Thread Tools Display Modes
  #1  
Old March 5th, 2006, 09:44 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

I need to update all values in a database column to new values. The
way it works is basically:

if = abc replace with 123
if = bcd replace with 234
if = cde replace with 345
if = def replace with 456

Now the values used above are just for example it's not really pattern
orientated. I can create four update queries for this, however was
hoping there was a way to do this in one shot. Is there a single
update query that could update the source values with new values based
on rules like the above?

Thanks.

JR

  #2  
Old March 5th, 2006, 10:51 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

IIF(tblName[FieldName] ='abc', '123', IIF(tblName[FieldName] = 'bcd', '234',
IIF(tblName[FieldName] = ...etc...)))

HTH;

Amy

"JR" wrote in message
oups.com...
I need to update all values in a database column to new values. The
way it works is basically:

if = abc replace with 123
if = bcd replace with 234
if = cde replace with 345
if = def replace with 456

Now the values used above are just for example it's not really pattern
orientated. I can create four update queries for this, however was
hoping there was a way to do this in one shot. Is there a single
update query that could update the source values with new values based
on rules like the above?

Thanks.

JR



  #3  
Old March 5th, 2006, 11:19 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

On 5 Mar 2006 12:44:55 -0800, "JR" wrote:

I need to update all values in a database column to new values. The
way it works is basically:

if = abc replace with 123
if = bcd replace with 234
if = cde replace with 345
if = def replace with 456

Now the values used above are just for example it's not really pattern
orientated. I can create four update queries for this, however was
hoping there was a way to do this in one shot. Is there a single
update query that could update the source values with new values based
on rules like the above?


Amy's suggestion is certainly one way if you don't have *too* many
values (nested IIF's will eventually be too complicated to handle).

Back up your database first, just in case of problems - update queries
are NOT reversible!

The next step up is to use the Switch() function: it takes arguments
in pairs, and if the first member of the pair is True it will return
the second argument and quit. That is, you could run an Update query
updating the field to

Switch([field] = "abc", 123,
[field] = "bcd", 234,
[field] = "cde", 345,
[field] = "def", 456,
... etcetera,
True, [Field])

The last value ensures that the field will be updated to its current
value (left unchanged in other words) if it doesn't match any of the
choices.

The next step up from *there* is to create a translation table with
two fields, the old and new values. Make the old value the Primary Key
of this table. Then create an Update query joining your current table
to the update table, change it to an Update query, and update to

[TranslationTable].[NewValue]

using your actual table and fieldname. The square brackets are
required (otherwise it will update all records to the text string
"TranslationTable.NewValue"!!!)

John W. Vinson[MVP]
  #4  
Old March 5th, 2006, 11:25 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

Create a table with the old values and the new values.

Then link that table to the existing table on the existing value to the old
value.

Now make an update the updates the fields to the new value

An SQL statement would look like the following.

UPDATE TableOriginal INNER JOIN TableNewValues
ON TableOriginal.TheField = TableNewValues.TheOldValues
SET TheField = TableNewValues.TheNewValues

Amy Blankenship wrote:

IIF(tblName[FieldName] ='abc', '123', IIF(tblName[FieldName] = 'bcd', '234',
IIF(tblName[FieldName] = ...etc...)))

HTH;

Amy

"JR" wrote in message
oups.com...
I need to update all values in a database column to new values. The
way it works is basically:

if = abc replace with 123
if = bcd replace with 234
if = cde replace with 345
if = def replace with 456

Now the values used above are just for example it's not really pattern
orientated. I can create four update queries for this, however was
hoping there was a way to do this in one shot. Is there a single
update query that could update the source values with new values based
on rules like the above?

Thanks.

JR

  #5  
Old March 5th, 2006, 11:38 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

Correction to SQL


UPDATE TableOriginal INNER JOIN TableNewValues
ON TableOriginal.TheField = TableNewValues.TheOldValues
SET TheField = [TableNewValues].[TheNewValues]

I forgot the all important square brackets in the update clause.

John Spencer wrote:

Create a table with the old values and the new values.

Then link that table to the existing table on the existing value to the old
value.

Now make an update the updates the fields to the new value

An SQL statement would look like the following.

UPDATE TableOriginal INNER JOIN TableNewValues
ON TableOriginal.TheField = TableNewValues.TheOldValues
SET TheField = TableNewValues.TheNewValues

Amy Blankenship wrote:

IIF(tblName[FieldName] ='abc', '123', IIF(tblName[FieldName] = 'bcd', '234',
IIF(tblName[FieldName] = ...etc...)))

HTH;

Amy

"JR" wrote in message
oups.com...
I need to update all values in a database column to new values. The
way it works is basically:

if = abc replace with 123
if = bcd replace with 234
if = cde replace with 345
if = def replace with 456

Now the values used above are just for example it's not really pattern
orientated. I can create four update queries for this, however was
hoping there was a way to do this in one shot. Is there a single
update query that could update the source values with new values based
on rules like the above?

Thanks.

JR

  #6  
Old March 7th, 2006, 12:13 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

Thanks...I tend to forget that there's an SQL version of the Select Case
statement.

"John Vinson" wrote in message
news
On 5 Mar 2006 12:44:55 -0800, "JR" wrote:

I need to update all values in a database column to new values. The
way it works is basically:

if = abc replace with 123
if = bcd replace with 234
if = cde replace with 345
if = def replace with 456

Now the values used above are just for example it's not really pattern
orientated. I can create four update queries for this, however was
hoping there was a way to do this in one shot. Is there a single
update query that could update the source values with new values based
on rules like the above?


Amy's suggestion is certainly one way if you don't have *too* many
values (nested IIF's will eventually be too complicated to handle).

Back up your database first, just in case of problems - update queries
are NOT reversible!

The next step up is to use the Switch() function: it takes arguments
in pairs, and if the first member of the pair is True it will return
the second argument and quit. That is, you could run an Update query
updating the field to

Switch([field] = "abc", 123,
[field] = "bcd", 234,
[field] = "cde", 345,
[field] = "def", 456,
... etcetera,
True, [Field])

The last value ensures that the field will be updated to its current
value (left unchanged in other words) if it doesn't match any of the
choices.

The next step up from *there* is to create a translation table with
two fields, the old and new values. Make the old value the Primary Key
of this table. Then create an Update query joining your current table
to the update table, change it to an Update query, and update to

[TranslationTable].[NewValue]

using your actual table and fieldname. The square brackets are
required (otherwise it will update all records to the text string
"TranslationTable.NewValue"!!!)

John W. Vinson[MVP]


  #7  
Old March 7th, 2006, 12:26 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

On Mon, 6 Mar 2006 17:13:13 -0600, "Amy Blankenship"
wrote:

Thanks...I tend to forget that there's an SQL version of the Select Case
statement.


Well, that's yet another alternative - but that's in SQL/Server T-SQL,
not in JET's peculiar dialect. The Switch() function is a VBA function
which can be called from a query, not native Access/JET SQL.

John W. Vinson[MVP]
  #8  
Old March 7th, 2006, 04:55 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

Maybe that's why I forget it. My SQL has to work from outside Access in
most cases

"John Vinson" wrote in message
...
On Mon, 6 Mar 2006 17:13:13 -0600, "Amy Blankenship"
wrote:

Thanks...I tend to forget that there's an SQL version of the Select Case
statement.


Well, that's yet another alternative - but that's in SQL/Server T-SQL,
not in JET's peculiar dialect. The Switch() function is a VBA function
which can be called from a query, not native Access/JET SQL.

John W. Vinson[MVP]



  #9  
Old March 7th, 2006, 01:29 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

Switch works outside Access, Amy. See the following URL ...

http://brenreyn.blogspot.com/

--
Brendan Reynolds
Access MVP

"Amy Blankenship" wrote in message
...
Maybe that's why I forget it. My SQL has to work from outside Access in
most cases

"John Vinson" wrote in message
...
On Mon, 6 Mar 2006 17:13:13 -0600, "Amy Blankenship"
wrote:

Thanks...I tend to forget that there's an SQL version of the Select Case
statement.


Well, that's yet another alternative - but that's in SQL/Server T-SQL,
not in JET's peculiar dialect. The Switch() function is a VBA function
which can be called from a query, not native Access/JET SQL.

John W. Vinson[MVP]





  #10  
Old March 7th, 2006, 09:11 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Update field based on current value

Cool work. Thanks for taking the time.

-Amy
"Brendan Reynolds" wrote in message
...
Switch works outside Access, Amy. See the following URL ...

http://brenreyn.blogspot.com/

--
Brendan Reynolds
Access MVP

"Amy Blankenship" wrote in message
...
Maybe that's why I forget it. My SQL has to work from outside Access in
most cases

"John Vinson" wrote in message
...
On Mon, 6 Mar 2006 17:13:13 -0600, "Amy Blankenship"
wrote:

Thanks...I tend to forget that there's an SQL version of the Select Case
statement.

Well, that's yet another alternative - but that's in SQL/Server T-SQL,
not in JET's peculiar dialect. The Switch() function is a VBA function
which can be called from a query, not native Access/JET SQL.

John W. Vinson[MVP]







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update a Foeld based on another Field Dominic General Discussion 10 January 5th, 2006 05:36 PM
Update a field in a query based on a user input RT Running & Setting Up Queries 4 July 6th, 2005 09:10 PM
Need Help In Printing Current Record in Specific Report RNUSZ@OKDPS Setting Up & Running Reports 1 May 16th, 2005 09:06 PM
How to loop through a recordset and update the field of a recordset or delete current record Karen Middleton Running & Setting Up Queries 1 January 4th, 2005 11:30 AM
Text Form Field Ref in Footer Won't Update on Screen StarWine General Discussion 3 December 6th, 2004 07:17 PM


All times are GMT +1. The time now is 10:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.