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

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12601-how-can-i-change-one-field.html
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 :
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12601

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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Add a new field to Table B along the lines of TownID, and an appropriate
data type.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12601

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
news:4MednZtsx797SDbZRVnyjw@bt.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12601
> 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
>
>

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12601
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

"Paul" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12601
>
> (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
>
>
>