Insert records in Parent and child tables

[Guest]

I have a spreadsheet which contains a parent name and 3 children inames n one
row. I want to import the data into access, I have a parent table and a child
table, both of these are linked with parent id as a foreign key in child
table.

How do I insert 1 row of data from excel into the parent tabel and 3 records
in Child table with the parent id as a foreign key ?

Any help on this greatly appreciated!

 

[Duane Hookom]

I would bring the records into a temporary table and then use an append
query to push the parent to the parent table. Then use a union query of the
children to append them to the child table.

 

[Guest]

Thank you Duane. I have created a temp table and have created an append to
the parent table. However, I have 3 columns, Child 1, Child 2 & Child 3 in
the temp table. Do I have to use a union query 3 times to add the child?
Also, How do I insert using a UNION query? Fairly new to Access.

Thanks again

Sujal

 

[Duane Hookom]

Assuming you have a primary key in your temp table named ID, your union
query would look something like:

SELECT ID, [Child 1] as Child
FROM tblTempImport
UNION ALL
SELECT ID, [Child 2]
FROM tblTempImport
WHERE [Child 2] is not null
UNION ALL
SELECT ID, [Child 3]
FROM tblTempImport
WHERE [Child 3] is not null;

Create an append query based on the union query.

 

[Guest]

Thank you! That worked, however, just one problem. I do have an ID in th temp
table, but the ID I require is from the Parents table as the foreign key in
Child table is the Parent ID. How can I retrieve the id From the Parents
file?

Thanks again!

Sujal

Assuming you have a primary key in your temp table named ID, your union
query would look something like:

SELECT ID, [Child 1] as Child
FROM tblTempImport
UNION ALL
SELECT ID, [Child 2]
FROM tblTempImport
WHERE [Child 2] is not null
UNION ALL
SELECT ID, [Child 3]
FROM tblTempImport
WHERE [Child 3] is not null;

Create an append query based on the union query.
--
Duane Hookom
MS Access MVP

Thank you Duane. I have created a temp table and have created an append to
the parent table. However, I have 3 columns, Child 1, Child 2 & Child 3 in
the temp table. Do I have to use a union query 3 times to add the child?
Also, How do I insert using a UNION query? Fairly new to Access.

Thanks again

Sujal

 

[Duane Hookom]

I generally have a field in the permanent table where I store the ID from
the temp table. This allows me to add the permanent table to my child append
query.
--
Duane Hookom
MS Access MVP

Thank you! That worked, however, just one problem. I do have an ID in th
temp
table, but the ID I require is from the Parents table as the foreign key
in
Child table is the Parent ID. How can I retrieve the id From the Parents
file?

Thanks again!

Sujal

Assuming you have a primary key in your temp table named ID, your union
query would look something like:

SELECT ID, [Child 1] as Child
FROM tblTempImport
UNION ALL
SELECT ID, [Child 2]
FROM tblTempImport
WHERE [Child 2] is not null
UNION ALL
SELECT ID, [Child 3]
FROM tblTempImport
WHERE [Child 3] is not null;

Create an append query based on the union query.
--
Duane Hookom
MS Access MVP

Thank you Duane. I have created a temp table and have created an append
to
the parent table. However, I have 3 columns, Child 1, Child 2 & Child 3
in
the temp table. Do I have to use a union query 3 times to add the
child?
Also, How do I insert using a UNION query? Fairly new to Access.

Thanks again

Sujal

:

I would bring the records into a temporary table and then use an
append
query to push the parent to the parent table. Then use a union query
of
the
children to append them to the child table.
--
Duane Hookom
MS Access MVP

I have a spreadsheet which contains a parent name and 3 children
inames
n
one
row. I want to import the data into access, I have a parent table
and a
child
table, both of these are linked with parent id as a foreign key in
child
table.

How do I insert 1 row of data from excel into the parent tabel and 3
records
in Child table with the parent id as a foreign key ?

Any help on this greatly appreciated!