Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Paul, Jul 28, 2006.

  1. Paul

    Paul
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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


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


    "Paul" <junkboypaul@hotmail.com> 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
    > 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
    >
    >
     
  4. Sprinks

    Sprinks
    Expand Collapse
    Guest

    RE: How can I change one field of every record in one table to the val

    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

    "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
    >
    > (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
    >
    >
    >
     

Share This Page