Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $95

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
    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page