advanced query to switch phone #'s in columns

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have 2 columns of phone #'s..actually 3 columns..they
look like this

516 651 4551 631 123 6545 516 333 6654
631 645 7777 516 321 5666 516 333 4556
516 546 8487 631 654 7845 631 454 5464


I need to come up with a query that will put all the 631
#'s in the 1st column with their corresponding 516 #'s in
the 2nd slot...their 3rd number will stay whatever it is.

When I run this I need it to look like this

631 123 6545 516 651 4551 516 333 6654
631 645 7777 516 321 5666 516 333 4556
631 654 7845 516 546 8487 631 454 5464

Any help would be great.

Thanks
Scott
 
Hi,


I do not see any easy way to do it in Jet, without normalization, or using
VBA.

With MS SQL Server, we may try the following ( make a backup and work on a
dummy set of data)


UPDATE tableName
SET field1 =field2, field2=field1
WHERE field2 LIKE '631%'



If you try this with Jet you will end up with copying field2 in field1, but
not in an exchange of the two columns: when it will be time to make
field2 = field1, Jet will use the actually modified value of field1, the
old value of field2, rather than the old value of field1. On the other hand,
MS SQL Server start the process by making an additional internal copy of the
record, and read from this internally stored copy when a field appear at the
right side of the assignment. Jet makes just one copy and modify it as the
text is written, so, when it reads field1, in field2=field1, it get the old
value of field2, not the old value of field1.



Hoping it may help,
Vanderghast, Access MVP
 
Thank you for this info. I am going to try this.

This will sound crazy but where do I put this code for the
MS SQL Server?

I am fairly new to this.

Thanks
Scott
 
Hi,


It may be dependant of you Access version. In mine I start creating a
Stored Proc, I bring the table I want (in my case, I brought Jobs from
Pubs), I changed the SELECT type to UPDATE table query type, from the
toolbar, I checked mark min_lvl and max_lvl then, in the grid, under
"NewValue", I typed max_lvl et min_lvl respectively (to exchange the
values).In the SQL pane, I could see:


UPDATE dbo.jobs
SET min_lvl = max_lvl, max_lvl = min_lvl


and executing the query (through clicking the Exclamation point on the
toolbar) , I get the data exchanged, effectively (after having to save the
sproc).


In your case, you may fill the Criteria, for the appropriate field: LIKE
'631%'



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top