import into multiple tables

[Guest]

Hi there

I have a one to many relationship between test(parent) and test details(child)
Users will be supplying me with a csv or delimited file of data. I want to programmactically import this data to the appropriate tables, honoring rules of integrity and key structures.

My plan was to programmically add the fields to the parent table, import the data, append the child records to tblTestResults and then programmatically delete the added fields on close.

Is this possible? Is there a better alternative?

Best Regards,

Carlee

 

[Joe Fallon]

I would not do it that way.
Import to a staging table that matches the file layout.
Then every time you import the file just clear the staging table first.

Then wirte 2 queries.
1. Add Header data to the correct table.
2. Add Detail data to the correct table.

Note your Header data is probably repeated for each row of detail data so
just get the first instance of it.
--
Joe Fallon
Access MVP

Hi there

I have a one to many relationship between test(parent) and test details(child)
Users will be supplying me with a csv or delimited file of data. I want

to programmactically import this data to the appropriate tables, honoring
rules of integrity and key structures.

My plan was to programmically add the fields to the parent table, import

the data, append the child records to tblTestResults and then
programmatically delete the added fields on close.

 

[Guest]

Hi Joe,

Thank you for your suggestion. i understand what you mean by header info for each detail record, but a little, or very unsure how to do that. Could you give me a bit more detail on how to accomplish this?

Also, if i used a staging table, how could i do this making sure that there are no key violations with the actual parent and child tables?

regards

 

[Joe Fallon]

You need some sample data: (the first 2 fields are in the Header, the next 3
are in the Detail.)

OrderId OrderDate LineNum PartNo Qty
1 8/3/2004 1 ABC 3
1 8/3/2004 2 DEF 5


Notice how the header data is repeated?
You import this to a staging table so it looks exactly like this but it is
now in Access.

When you write a query to "move" the header data you append the first two
fields to your header table.
(The PK in your header table will prevent you from adding duplicates.)
(You need to use a Totals query to "collapse" each header record into a
single row. There are two identical rows in the example and you only need
one.)

The same idea applies to the details. You take the OrderId and the last 3
fields and append them to your details table.
--
Joe Fallon
Access MVP

Hi Joe,

Thank you for your suggestion. i understand what you mean by header info

for each detail record, but a little, or very unsure how to do that. Could
you give me a bit more detail on how to accomplish this?

Also, if i used a staging table, how could i do this making sure that

there are no key violations with the actual parent and child tables?