Updating Multiple records based on values

[TexasStar]

Hello, I have searched for help on this but it just has me stumped. I
tried doing several different things, but none of them worked.

Here is the Scenario:

I have a table called Table1 and that table has several columns. Two
of the columns are titled 'State' and the other is titled 'API'.

The State fields are blank, and the API fields contain a number in
this format for example: 42-321-4567

What I am trying to do is update each State field based on the first 2
digits of the API number. I want to read each API, determine what 2
digit state code is represents and then update the State field with
the correct name for that state.

Can someone point me in the right direction and give me some help in
accomplishing this? Any help or advice would be greatly appreciated. I
just can't crack this and I feel like it should be something pretty
simple. I was thinking about setting up cases depending on what 2
digit code is trimmed and then run a special SQL update to set the
State field correctly, but I haven't been able to get it working. If
anyone could code me up an example that I could expand on it would be
greatly appreciated.

 

[John Spencer]

Build a table of the two digit codes and the State name or abbreviation

Then use the LEFT function to grab the first two character and match on
that in an UPDATE query. The SQL statement for the update query might
look something like


UPDATE Table1 INNER JOIN StateTable
ON Left(Table1.API,2) = StateTable.APICode
SET Table1.StateField = [StateTable].[StateName]



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[TexasStar]

Build a table of the two digit codes and the State name or abbreviation

Then use the LEFT function to grab the first two character and match on
that in an UPDATE query. The SQL statement for the update query might
look something like

UPDATE Table1 INNER JOIN StateTable
ON Left(Table1.API,2) = StateTable.APICode
SET Table1.StateField = [StateTable].[StateName]

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello, I have searched for help on this but it just has me stumped. I
tried doing several different things, but none of them worked.

Here is the Scenario:

I have a table called Table1 and that table has several columns. Two
of the columns are titled 'State' and the other is titled 'API'.

The State fields are blank, and the API fields contain a number in
this format for example: 42-321-4567

What I am trying to do is update each State field based on the first 2
digits of the API number. I want to read each API, determine what 2
digit state code is represents and then update the State field with
the correct name for that state.

Can someone point me in the right direction and give me some help in
accomplishing this? Any help or advice would be greatly appreciated. I
just can't crack this and I feel like it should be something pretty
simple. I was thinking about setting up cases depending on what 2
digit code is trimmed and then run a special SQL update to set the
State field correctly, but I haven't been able to get it working. If
anyone could code me up an example that I could expand on it would be
greatly appreciated.- Hide quoted text -

- Show quoted text -

John,

That worked perfectly and was far cleaner and simplier then anything I
was trying to do. Thank you for taking the time to answer my question
and working up an example for me! And sorry again for the double posts!