How can I change one field of every record in one table to the value of another?

[Paul]

Hi,

I have just created a table (TABLE A) with the following fields (id :
autonumber, town : text) which contains a list of towns, cities, etc. to go
along with another table (TABLE B) filled with many fields including a town
field which contains town names, but i wish to replace the town field names
in TABLE B with the id number in TABLE A, if the town of TABLE A matches the
town of TABLE B.

(e.g - Table A)
ID TOWN

1 London
2 Bristol
3 Coventry

(Table B)
TOWN

London
Bristol
London
Coventry
Coventry

TABLE B needs to be changed to :

TOWN

1
2
1
3
3


Is there any way it can be automatically changed? as there are thousands of
records I need to amend!

Thanks

 

[Douglas J Steele]

Add a new field to Table B along the lines of TownID, and an appropriate
data type.

Write an Update query to populate TownID based on what's currently in the
Town field.

The query will look something like:

UPDATE [Table B] INNER JOIN [Table A]
ON [Table B].[Town] = [Table A].[Town]
SET TownID = [Table A].ID

 

[Sprinks]

Paul,

After performing the Update as Doug suggests, it's likely that there will
exist records that didn't match, either due to the town not being present in
Table A, or because of misspellings in Table B. These records will have a
Null value in the TownID field; hopefully a small number of them.

I suggest you go through these manually, adding a corresponding Table A
record if necessary. Once all records have a valid TownID, then you can
delete the Town text field.

Sprinks