update query for multiple changes to a single field

[Guest]

hi all,
i have a access table, service_calls, which has a filed named call_type.

the call_type filed will have a 1 or 2 character code for each record in the
table.

i am looking for help to create an update query which will allow me to
change the values in the call_type column with a single pass.

example: the call type field will contain the following codes:
D, X, SH, K, 4, I, 3, PM, EM, L

i want to do a single pass update that will change the codes to :

D001, X001, SH01, K001, 4001, I001, 3001, PM01, EM01, L001

i know i can just change the criteria in the grid for each code and run the
query multiple times, but with approximated 100 codes to change that could
get boring.

i need to update about 172k records.

as always, any help will be greatly appreciated.

 

[Guest]

STANDARD WARNING: Make a backup of any tables that you update first or even
the entire database.

UPDATE service_calls
SET service_calls.call_type = [call_type] & String(3-Len([call_type]),"0") &
"1";

 

[Jason Lepack]

update
service_calls
set
call_type = [call_type] & iif(len([call_type])=2,'01','001')

 

[Jason Lepack]

Sorry, this is Ms Access, it should be:

update
service_calls
set
call_type = [call_type] & iif(len([call_type])=2,"01","001")

 

[Guest]

thanks guys for the very quick replies.

another question along the same lines. what if i wanted to change the values
to something totally different.

example, i want to change call_type D to 1AB, call_type X to 4CSA, call_type
SH to A1, etc.

thanks again.

Sorry, this is Ms Access, it should be:

update
service_calls
set
call_type = [call_type] & iif(len([call_type])=2,"01","001")

hi all,
i have a access table, service_calls, which has a filed named call_type.

the call_type filed will have a 1 or 2 character code for each record in the
table.

i am looking for help to create an update query which will allow me to
change the values in the call_type column with a single pass.

example: the call type field will contain the following codes:
D, X, SH, K, 4, I, 3, PM, EM, L

i want to do a single pass update that will change the codes to :

D001, X001, SH01, K001, 4001, I001, 3001, PM01, EM01, L001

i know i can just change the criteria in the grid for each code and run the
query multiple times, but with approximated 100 codes to change that could
get boring.

i need to update about 172k records.

as always, any help will be greatly appreciated.

 

[Steve]

It would take a separate update query for each change. You could use the
same query and just modify the query for each change. To change call_type D
to 1AB, you would set the criteria for call_type to "D" and set Update To to
"1AB". Quotes are needed in both. After you run this query, you change the
criteria for call_type and reset Update TO for the next change.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

thanks guys for the very quick replies.

another question along the same lines. what if i wanted to change the
values
to something totally different.

example, i want to change call_type D to 1AB, call_type X to 4CSA,
call_type
SH to A1, etc.

thanks again.

Sorry, this is Ms Access, it should be:

update
service_calls
set
call_type = [call_type] & iif(len([call_type])=2,"01","001")

hi all,
i have a access table, service_calls, which has a filed named
call_type.

the call_type filed will have a 1 or 2 character code for each record
in the
table.

i am looking for help to create an update query which will allow me to
change the values in the call_type column with a single pass.

example: the call type field will contain the following codes:
D, X, SH, K, 4, I, 3, PM, EM, L

i want to do a single pass update that will change the codes to :

D001, X001, SH01, K001, 4001, I001, 3001, PM01, EM01, L001

i know i can just change the criteria in the grid for each code and run
the
query multiple times, but with approximated 100 codes to change that
could
get boring.

i need to update about 172k records.

as always, any help will be greatly appreciated.

 

[Guest]

thanks steve,
i thought i would probably have to do it that way, but hope springs eternal
and i was hoping there might be a way to do them all at once.

thanks again

It would take a separate update query for each change. You could use the
same query and just modify the query for each change. To change call_type D
to 1AB, you would set the criteria for call_type to "D" and set Update To to
"1AB". Quotes are needed in both. After you run this query, you change the
criteria for call_type and reset Update TO for the next change.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

thanks guys for the very quick replies.

another question along the same lines. what if i wanted to change the
values
to something totally different.

example, i want to change call_type D to 1AB, call_type X to 4CSA,
call_type
SH to A1, etc.

thanks again.

Sorry, this is Ms Access, it should be:

update
service_calls
set
call_type = [call_type] & iif(len([call_type])=2,"01","001")

hi all,
i have a access table, service_calls, which has a filed named
call_type.

the call_type filed will have a 1 or 2 character code for each record
in the
table.

i am looking for help to create an update query which will allow me to
change the values in the call_type column with a single pass.

example: the call type field will contain the following codes:
D, X, SH, K, 4, I, 3, PM, EM, L

i want to do a single pass update that will change the codes to :

D001, X001, SH01, K001, 4001, I001, 3001, PM01, EM01, L001

i know i can just change the criteria in the grid for each code and run
the
query multiple times, but with approximated 100 codes to change that
could
get boring.

i need to update about 172k records.

as always, any help will be greatly appreciated.

 

[John W. Vinson]

example, i want to change call_type D to 1AB, call_type X to 4CSA, call_type
SH to A1, etc.

You could make up a little two-field translation table with D in one field,
1AB in the other, etc. through all the changes; make the first field -
Call_type - the Primary Key and call the other field NewType. Create an Update
query joining your main table to this table, joining on Call_type. Update your
current Call_type to


[TranslationTable].[NewType]

The brackets are essential.


John W. Vinson [MVP]