update one field from another

[Guest]

I know this will probably be an update query but my problem is how to. When
I imported several hundred rows from my EXCEL sheets they did not have one of
the fields we needed, the field "Location" in my table "Events" now how do I
fill the "Location" field by whats in my "Code" field in my "Events" table.
My Code field has 40 different codes and these codes have a specific location
here how can I have COD put in the "Location" field when the "Code" field has
100SW in it. I am using ACCESS 2003.

 

[John W. Vinson]

I know this will probably be an update query but my problem is how to. When
I imported several hundred rows from my EXCEL sheets they did not have one of
the fields we needed, the field "Location" in my table "Events" now how do I
fill the "Location" field by whats in my "Code" field in my "Events" table.
My Code field has 40 different codes and these codes have a specific location
here how can I have COD put in the "Location" field when the "Code" field has
100SW in it. I am using ACCESS 2003.

Ummm... you probably *shouldn't* even have a location field in your Events
table.

Can you not instead just create a query joining Events to the table containing
the COD and Location? Storing the location redundantly is probably a Bad Idea.

John W. Vinson [MVP]

 

[Guest]

I orginally had the "Location" field in my Products table because the
products were always at the same location (location is the facility where the
products were unloaded), now with our upgrades products ("Code" field) are
now unloaded at several different facilities so I thought that putting the
"Location" field in the events table we could query the events to see what
locations a perticular products was unloaded.

 

[John W. Vinson]

I orginally had the "Location" field in my Products table because the
products were always at the same location (location is the facility where the
products were unloaded), now with our upgrades products ("Code" field) are
now unloaded at several different facilities so I thought that putting the
"Location" field in the events table we could query the events to see what
locations a perticular products was unloaded.

But you can *DO* that without storing the data redundantly! Or maybe I'm
misunderstanding. How (logically) are codes and locations related? Is there a
Codes table with a field for the location of each code? Might a code pertain
to more than one location? or might a location apply to more than one code? or
both?

John W. Vinson [MVP]

 

[Guest]

John, I now have a 'LocationCode" field in my events table. Up until a couple
of months ago every product was loaded or unloaded at only one Location so a
LocationCode was of no use to us but now with the upgrades several of the
products are loaded or unloaded at several of the locations. When we started
the diversity process I added a "LocationCode" field to my events table this
is the only field for location in my events table (I have a table for
"Product" and a table for "Locations"). What we are going to do is now have
to track how many times a perticular product is handled at a given location.
I need to associate the ealier products to a location without having to going
thru and do them one at a time. The ones from earlier this year are done in
only one location so if I can get the LocationCode to match ProductCode I'll
be alright

example
"ProductCode" has in it -- PG64 (same code thru out the year at the same
location until the upgrades)
"LocationCode" needs to say -- HOD (I can query and get the ones that has a
blank "LocationCode" because some of them now has the code aready in them)

I know I'm long winded but any help will be appreciated.

 

[John W. Vinson]

I need to associate the ealier products to a location without having to going
thru and do them one at a time. The ones from earlier this year are done in
only one location so if I can get the LocationCode to match ProductCode I'll
be alright

example
"ProductCode" has in it -- PG64 (same code thru out the year at the same
location until the upgrades)
"LocationCode" needs to say -- HOD (I can query and get the ones that has a
blank "LocationCode" because some of them now has the code aready in them)

For a one-shot operation, you can use an Update query updating the Location
field to the results of a SWITCH function. Create a Query based on your table;
select the LocationCode and ProductCode fields. Put a criterion of IS NULL on
LocationCode. Change the query to an Update query and on the UpdateTo line
under LocationCode put

=Switch([ProductCode] = "P64", "HOD",
[ProductCode] = "100SW", "COD",
[ProductCode] = "XYZ", "AAA",
.... <and so on>
)

Better might be to create a little 40 row table with the needed translations
and use it in a JOIN in an update query, but the Switch() should work.

John W. Vinson [MVP]

 

[Guest]

John, it work perfect it only took me a few minutes to change several hundred
records THANK YOU A THOUSAND TIMES OVER.

I need to associate the ealier products to a location without having to going
thru and do them one at a time. The ones from earlier this year are done in
only one location so if I can get the LocationCode to match ProductCode I'll
be alright

example
"ProductCode" has in it -- PG64 (same code thru out the year at the same
location until the upgrades)
"LocationCode" needs to say -- HOD (I can query and get the ones that has a
blank "LocationCode" because some of them now has the code aready in them)

For a one-shot operation, you can use an Update query updating the Location
field to the results of a SWITCH function. Create a Query based on your table;
select the LocationCode and ProductCode fields. Put a criterion of IS NULL on
LocationCode. Change the query to an Update query and on the UpdateTo line
under LocationCode put

=Switch([ProductCode] = "P64", "HOD",
[ProductCode] = "100SW", "COD",
[ProductCode] = "XYZ", "AAA",
.... <and so on>
)

Better might be to create a little 40 row table with the needed translations
and use it in a JOIN in an update query, but the Switch() should work.

John W. Vinson [MVP]

 

[John W. Vinson]

John, it work perfect it only took me a few minutes to change several hundred
records THANK YOU A THOUSAND TIMES OVER.

You're welcome, Tankerman; glad to have been of assistance!

John W. Vinson [MVP]