none
How do you pull out primary keys from one table into another RRS feed

  • Question

  • Hi I have two tables

    Table A: has names of people that appear only once in the column of the table. Each name is associated with its own Auto generated Primary key 

    Table B: Has the names of the above people occurring multiple times in the same column. 

    I would like to create an association between tables A and B. My thinking is to pull out the primary key from Table A, into a separate column in Table B by comparing the names of people in Table B with Table A. Is this the right thing to do and how do I do it? If this is not the right thing to do can you please make suggestions?

    Thanks for your help

    • Edited by Rian78 Tuesday, August 18, 2015 2:56 PM
    Tuesday, August 18, 2015 2:50 PM

Answers

  • Open table B in design view and add the foreign key column if it doesn't exist already.

    Save and close table B.

    On the Create tab of the ribbon, click Query Design.

    Add table A and table B, then close the Show Table dialog.

    If Access has automatically joined the tables on the primary key / foreign key fields, delete this join (click once on the join line, then press Delete).

    Join the tables on the name field.

    On the Design tab of the ribbon, in the Query Type group, click Update.

    Add the foreign key field from table B to the query grid.

    In the Update to row of this field, enter the expression

    [table A].[primary key field]

    where 'table A' is the name of, well, table A and 'primary key field' is the name of - you get the idea.

    Now click the Run button (the red exclamation mark) and confirm that you want to update ... records.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Tuesday, August 18, 2015 3:58 PM to correct stupid error (thanks, the DB guy!)
    • Marked as answer by Rian78 Tuesday, August 18, 2015 4:03 PM
    Tuesday, August 18, 2015 3:04 PM

All replies

  • Hi. If the names match exactly in both tables, you can join them using the text name columns and transfer the ID numbers into table B. After that, you can delete the name columns and keep the new ID column as a foreign key. So, add a numeric column to table B called fkID (for example) and run an UPDATE query similar to the following:

    UPDATE TableB
    INNER JOIN TableA
    ON TableB.NameField=TableA.NameField
    SET TableB.fkID=TableA.ID

    Hope that helps...
    Tuesday, August 18, 2015 2:57 PM
  • Open table B in design view and add the foreign key column if it doesn't exist already.

    Save and close table B.

    On the Create tab of the ribbon, click Query Design.

    Add table A and table B, then close the Show Table dialog.

    If Access has automatically joined the tables on the primary key / foreign key fields, delete this join (click once on the join line, then press Delete).

    Join the tables on the name field.

    On the Design tab of the ribbon, in the Query Type group, click Update.

    Add the foreign key field from table B to the query grid.

    In the Update to row of this field, enter the expression

    [table A].[primary key field]

    where 'table A' is the name of, well, table A and 'primary key field' is the name of - you get the idea.

    Now click the Run button (the red exclamation mark) and confirm that you want to update ... records.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Tuesday, August 18, 2015 3:58 PM to correct stupid error (thanks, the DB guy!)
    • Marked as answer by Rian78 Tuesday, August 18, 2015 4:03 PM
    Tuesday, August 18, 2015 3:04 PM
  • In the Update to row of this field, enter the expression

    [table A].[name field]

    where 'table A' is the name of, well, table A and 'name field' is the name of - you get the idea.

    Just to clarify a bit... I believe Hans meant that to be: [table A].[primary key field]

    Sorry if I read it wrong...

    Tuesday, August 18, 2015 3:10 PM
  • So your tables should look something like the following:

    TableA

    Field Name

    Data Type

    Required

    Unique

    Key

    Description

    ID

    AutoNumber

    Yes

    Yes

    PK

    Record ID

    NameOfPerson

    Text (50)

    Yes

    Yes

    Person's name

    TableB

    Field Name

    Data Type

    Required

    Unique

    Key

    Description

    ID

    AutoNumber

    Yes

    Yes

    PK

    Record ID

    NameOfPerson

    Text (50)

    Yes

    No

    To be deleted

    TableA_ID

    Long Integer

    Yes

    No

    FK

    New - FK related to TableA

    Given the above, your update query should look like the following:

    SQL:

    UPDATE TableA INNER JOIN TableB ON TableA.NameOfPerson = 
    TableB.NameOfPerson SET TableB.TableA_ID = [TableA].[ID];

    When you have finished updating TableB, you will want to define your relationship between the two tables.  It should look like the following:


    • Edited by RunningManHD Tuesday, August 18, 2015 3:55 PM
    • Proposed as answer by André Santo Tuesday, August 18, 2015 3:55 PM
    Tuesday, August 18, 2015 3:44 PM
  • Yes of course - stupid error from me. Thanks!

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 18, 2015 3:57 PM
  • One more thing, you might want to create a SELECT query to ensure that all your names match between tables.  It is possible that spelling errors may exist that will fail to match.

    SQL:

    SELECT TableB.ID, TableB.NameOfPerson, TableA.ID
    FROM TableA RIGHT JOIN TableB ON TableA.NameOfPerson = TableB.NameOfPerson
    WHERE (((TableA.ID) Is Null));

    Table data and Query Results:



    Tuesday, August 18, 2015 4:06 PM
  • Thank you for the detailed steps!
    Tuesday, August 18, 2015 4:10 PM