Appending One Table into a Parent Table and Child Table

[Nanette]

I imported data from an Excel spreadsheet into an Access Temporary Table so
that I can import that data into a Parent table and then a Child table.

The import to the Parent table was successful.

Now I need to import data to the Child table and still have referential
integrity up and running. How do I get the Parent PK field over to the Child
FK field in a correct manner with the corresponding data?

I'm new at this so any detailed info you can provide would be helpful.

 

[Jeff Boyce]

Nanette

When you "loaded" the Parent table, did you keep track of the ParentID that
was assigned?

If you have a way to join your temporary table and your parent table on
unique fields, you could "look up" the ParentID that was assigned.

Once you have a query that does that, use that query as the starting point
for another query that takes the Child data, along with the ParentID, and
appends those records to the Child table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Nanette]

Jeff,

When you "loaded" the Parent table, did you keep track of the ParentID that

was assigned?

When I loaded the Parent table, a number from autonumber was assigned to
each record. Is this what you mean?

If you have a way to join your temporary table and your parent table on
unique fields, you could "look up" the ParentID that was assigned. How is this done?

Once you have a query that does that, use that query as the starting point
for another query that takes the Child data, along with the ParentID, and
appends those records to the Child table.

Can you tell me how to do this?

 

[Jeff Boyce]

Nanette

see comments in-line below...

Jeff,

When you "loaded" the Parent table, did you keep track of the ParentID
that

When I loaded the Parent table, a number from autonumber was assigned to
each record. Is this what you mean?

No. I was asking if you know which autonumbers were assigned to which "old"
parent data/rows?

How is this done?

If you are confident that you have a combination of fields (for example,
LastName, FirstName, Address) that will uniquely identify each row in your
old data, and will be in your new Parent table, create a query, add those
two tables, and join on those fields. Then add the new Parent table's ID
field to "see" the ID that was assigned.

Can you tell me how to do this?

See previous explanation for how to create the first query. Then start
another query. Add the input (raw) data source, and add the first query.
Join these, and include the fields you'll want to see in the Child records.
Be sure to include the ParentID. When the query (a select query) is
returning the data you want, convert it to an append query, and append to
the Child table.

Regards

Jeff Boyce
Microsoft Office/Access MVP