Foreign key field in table does not automatically populate from main table

[wardwebber]

I'm building an Access 2007 database by pasting field data from Excel 2007 into corresponding fields/tables in Access. The main Access table, AllDeathRecords , has an autonumbered primary key of PersonID. A second table, MultipleObituar ies, has a PersonID field linked to the AllDeathRecords table. It's a one-to-many relationship; one person can have many obituaries.

My problem is that I've populated the AllDeathRecords table, so now it has many autonumbered PersonIDs, and because of the table relationship, I expected the MultipleObituar ies table to show a long list of records, one for each PersonID, with otherwise blank fields in each. Then I could copy/paste from Excel to fill the other fields.

But the MultipleObituar ies table is empty. How can I get it to fill in the PersonIDs from the main table? Or can I just append them manually?

[jimatqsi]

Sorry, wardwebber, it's not quite that automatic. Just you setting up that relationship does nothing to make records in one table magically appear in another. It does, however, prevent records from being in the MultipleObituar ies table without a matching PersonID in the AllDeathRecords table. The AllDeathTable record must be entered before the other table can be populated for that person.

Do you know that you can link to your Excel sheet from Access. If you did that, and then wrote an append query, you could create your obituaries from the Excel sheet automatically.

Check out this search on the bytes site, study Access help about append queries, try a few things and then come back with more questions.
Search on Google for linking to excel from access site:bytes.com

Jim

[wardwebber]

Thanks, Jim. Just wanted to ensure there wasn't an easier way. I ended up just pasting the PersonIDs manually into the MultipleObituar ies table; I'll fill in the rest of the fields that way as well. I don't want to link to the Excel sheet, because it's not being maintained, but thanks for the tip anyway.