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  

Shift several columns in table



 
 
Thread Tools Display Modes
  #1  
Old May 26th, 2010, 02:51 PM posted to microsoft.public.access.queries
Hans[_7_]
external usenet poster
 
Posts: 1
Default Shift several columns in table

Hi!

Say you have a table A with fields field1, field2, field3 and field4 and I
want to shift values so field1 receives the value from field2, field2
receives the value from field3 and field3 receives the value from field4
(field4 should keep it's value) will a query like

Update A set field1=field2, field2=field3, field3=field4

do the job? Will the right hand side of the assignments always have the
"old" value before the update? What I'm affraid of is that I set
field1=field2 but in the same statement updates field2 will access use the
updated value of field2 and put that into field1? When I test it seems to
work as I want but I would like someone confirm this.

The alternative would be to run three queries and just update one field at a
time but if the right hand side of the assignment always have the "old"
value then I can do the update in one query which will gain performance in
my case.

Regards
/Hans


  #2  
Old May 26th, 2010, 03:03 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Shift several columns in table

I believe that you can use
Update A set field1=field2, field2=field3, field3=field4
and it will work. In my experience it has worked in the past, but I have not
had the need to do something like this since Access 97. The need to do
something like this suggests to me that you have a table design problem or are
stuck with data from another source that you need to normalize.

I would test it after making a backup of the table and adding a where clause
to do one record. After all the database engine you are using may have been
updated and the behavior changed.

Update A
set field1=field2, field2=field3, field3=field4
WHERE PrimaryKey = somespecificValue


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hans wrote:
Hi!

Say you have a table A with fields field1, field2, field3 and field4 and I
want to shift values so field1 receives the value from field2, field2
receives the value from field3 and field3 receives the value from field4
(field4 should keep it's value) will a query like

Update A set field1=field2, field2=field3, field3=field4

do the job? Will the right hand side of the assignments always have the
"old" value before the update? What I'm affraid of is that I set
field1=field2 but in the same statement updates field2 will access use the
updated value of field2 and put that into field1? When I test it seems to
work as I want but I would like someone confirm this.

The alternative would be to run three queries and just update one field at a
time but if the right hand side of the assignment always have the "old"
value then I can do the update in one query which will gain performance in
my case.

Regards
/Hans


  #3  
Old May 26th, 2010, 03:59 PM posted to microsoft.public.access.queries
Krzysztof Naworyta
external usenet poster
 
Posts: 80
Default Shift several columns in table

Juzer Hans napisał

| Say you have a table A with fields field1, field2, field3 and field4
| and I want to shift values so field1 receives the value from field2,
| field2 receives the value from field3 and field3 receives the value
| from field4 (field4 should keep it's value) will a query like
|
| Update A set field1=field2, field2=field3, field3=field4
|
| do the job? Will the right hand side of the assignments always have the
| "old" value before the update? What I'm affraid of is that I set
| field1=field2 but in the same statement updates field2 will access use
| the updated value of field2 and put that into field1? When I test it
| seems to work as I want but I would like someone confirm this.

Yes, you can.

You can even replace values in two fields:

Update Table1
Set
Field1 = Field2
, Field2 = Field1

--
KN

  #4  
Old May 26th, 2010, 04:53 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Shift several columns in table

I'm with John ... why? You've explained "how" you're trying to solve some
business need, but not what the need is.

If you'll provide a bit more specific description of "what", folks here may
be able to offer alternate approaches...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Hans" wrote in message
...
Hi!

Say you have a table A with fields field1, field2, field3 and field4 and I
want to shift values so field1 receives the value from field2, field2
receives the value from field3 and field3 receives the value from field4
(field4 should keep it's value) will a query like

Update A set field1=field2, field2=field3, field3=field4

do the job? Will the right hand side of the assignments always have the
"old" value before the update? What I'm affraid of is that I set
field1=field2 but in the same statement updates field2 will access use the
updated value of field2 and put that into field1? When I test it seems to
work as I want but I would like someone confirm this.

The alternative would be to run three queries and just update one field at
a time but if the right hand side of the assignment always have the "old"
value then I can do the update in one query which will gain performance in
my case.

Regards
/Hans



  #5  
Old May 26th, 2010, 05:47 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Shift several columns in table

Hans wrote:
Hi!

Say you have a table A with fields field1, field2, field3 and field4
and I want to shift values so field1 receives the value from field2,
field2 receives the value from field3 and field3 receives the value
from field4 (field4 should keep it's value) will a query like

Update A set field1=field2, field2=field3, field3=field4

do the job? Will the right hand side of the assignments always have
the "old" value before the update? What I'm affraid of is that I set
field1=field2 but in the same statement updates field2 will access
use the updated value of field2 and put that into field1? When I test
it seems to work as I want but I would like someone confirm this.

The alternative would be to run three queries and just update one
field at a time but if the right hand side of the assignment always
have the "old" value then I can do the update in one query which will
gain performance in my case.



It will work as you wish, but I would not bother doing it. I would
rename the table "DoNotUse" and create a query with the original table's
name that selects the data from DoNotUse, assigning the "correct" names
to the columns using aliases.

--
HTH,
Bob Barrows


 




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


All times are GMT +1. The time now is 04:48 PM.


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