Update question

  • Thread starter Thread starter FJB
  • Start date Start date
F

FJB

I have a table which has a field called "Center Codes" and another
table with the same filed name. In order for these two fields to match,
I believe I need to update the original table by adding three numbers
in from of each record that has information in it (not all records have
this field completed). Is there a way to add those three numbers
without having to touch each record?

A related question, is it possible for for a field in Table A to match
with a field in Table B when the information in Table A matches part
of the information in Table B? The matching part would be the same six
numbers at the end of the center code if the company number is added at
the beginning.

For example, Table A has "8000450" in the center code field while Table
B has "1758000450". I either need to update each record in Table A
which has data by adding 175 to the beginning of the field OR some way
let these two values match.

Hope this makes sense.

I appreciate your help with this and other issues I have brought to
this group.

Frank
 
Frank:

My first choice (i.e. my preference) would be to create a query from Table B
which includes all fields from Table B and one extra field, which I'll call
CenterCodeMatch. The query might look something like the following:

SELECT Field1, Field2, ..., Right([CenterCode],7) as CenterCodeMatch
FROM TableB

I would then join this query with Table A, joining the Center Code field
from Table A with the CenterCodeMatch field. I do not know the data types
of your Center Code fields so you may have to adjust the data types using
the "C" functions (CInt, CDbl, CStr, etc.). You may also have to adjust for
the possibility of null values (IsNull function). This should allow you to
match the Center Codes from the two tables.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a table which has a field called "Center Codes" and another
table with the same filed name. In order for these two fields to match,
I believe I need to update the original table by adding three numbers
in from of each record that has information in it (not all records have
this field completed). Is there a way to add those three numbers
without having to touch each record?

A related question, is it possible for for a field in Table A to match
with a field in Table B when the information in Table A matches part
of the information in Table B? The matching part would be the same six
numbers at the end of the center code if the company number is added at
the beginning.

For example, Table A has "8000450" in the center code field while Table
B has "1758000450". I either need to update each record in Table A
which has data by adding 175 to the beginning of the field OR some way
let these two values match.

Hope this makes sense.

I appreciate your help with this and other issues I have brought to
this group.

Frank
 
Thanks for the suggestion. I will try it. This will fix the current
center codes but I am also concerned about any new ones. You can set up
a mask for inputting but it is possible to input the 6 digit center
codes and automatically copy the value of that field into another field
with a mask adding 175? I tried this in Excel and it worked.

Any suggestions are welcome.

Thanks

Frank
 
Back
Top