Help! Populating foreign key field

[wazimu13]

Hi

i have made two tables with data from an excel sheet. The excel sheet has many duplicates and im trying to eliminate this. The tables are:

tblTasks and tblTeam. Both have autoincrementing primary keys, and the tblTasks table has the TeamID (primary key from tblTeam) as a foreign key.

My question is, how do i populate the TeamID field via perhaps a query, as it is blank on all records. I have over 5000 tasks so a manual approach is what im trying to avoid. A sample of the fields in the tables is as follows:

tblTasks

TaskID - PK
Task Name
Team ID - FK


tblTeam
TeamID - PK
Team Name

Not sure this will help, but theres also a Staff table. tblTeam has a one to many relationship with the Staff table. tblTeam also has a one to many relationship with the tblTasks table.

 

[Alansidman]

cross posted at: http://www.excelforum.com/access-tables-and-databases/968965-help-populating-foreign-key-field.html

Please read this: http://www.excelguru.ca/content.php?184

 

[Mihail]

Hi Alan,
I followed your second link, I read all then, because I think that there are good reasons, I tried to answer to the OP question in the forum (first link) but... that forum required to be a registered user and... I don't like to do this . So, there are some issues with this cross posts.

@ wazimu
Try to solve the issues in Excel. Then, after you have clean tables, you can move to Access.

 

[Pat Hartman]

You can do this in either of two ways which ever is easiest.
1. After you create the one-side table. Add a new column to the imported spreadsheet. Create an update query by joining the one-side table back to the original imported spreadsheet and updating the new key column with the autonumber from the one-side table. That way when you create the many-side table you will have the FK available to append immediately.
2. Create the one-side table and then create the many-side table. Create a query that joins the two on several of the identifying columns of the one-side table and update the FK with the PK from the joined table.

Regardless of which method you chose, Create an unmatched query when you are done to make sure all the many-side records got a FK assigned.