VBA to make 1 row in tableA become row in B & rows in C (1 Viewer)

[LisaAtWork]

I have an existing database being used for for order processing (normalized, working). The order table houses the general order info and a separate detail table holds 1 or more orderdetail records for each order in the order table, related by orderID.

A user has asked that I find a way to import her data into the system from an excel spreadsheet. She has individual columns for each type of order detail record, so for each "order" row, there may be 1 or more columns of "orderdetail"s that I will need to parse into the correct tables.

I imported the raw excel into a table, but I need to append that data into the order and orderdetail table rows (i.e. I have to create the order and orderdetail records that match every other record in the system).

Can someone offer any sample code in VBA that does something similar?
It looks like I need to somehow perform a looped INSERT INTO [ORDER] (field1, field2....) VALUE (val1, val2) but there are those related detail records to contend with (the orderdetail table entries) which may be more than one insert...I'm a over my head, knowledge-wise.

A testset of data is attached.

Lisa

 

[jdraw]

If your access tables are normalized, and they seem to be based on your description of Order and Orderdetail, then the following process "in general" may be the way to approach your issue.

Excel spreadsheet data is not usually normalized. Often many columns in a row may refer to more than 1 "Access table".
What is usually done is:

Create a temporary "holding tank table" in access to contain the info from the rows in Excel.
Set up some queries in Access to pick up the info from the temporary table. These are typically Append queries and do not involve all fields in the temporary table record.

In your example, you may have to create a new Order record. You pick the fields in the temp table that relate to Order, and create the new Order.

With the new Order created you can now pick up the fields relating to order item(s) for that Order. You Append/Insert these order items into the Order table and include the OrderId as a FK.

That's the 30000 ft overview. Hope it helps.

 

[LisaAtWork]

Feels like there you should be a way to write a nested loop that handles this, but I'm not familiar enough with the SQL language part of it to be certain. Worst case I could always take 2 full swipes at the data: first insert the order, then take another run through the to pick up the orderdetail rows for the now existing order.

 

[jdraw]

Well you only update one table at a time, and you can't update the children until the is an existing parent.
So Orders is the parent and OrderDetail is the child.

With those facts, what do you propose?

Whatever you do, you have to consider the data that came from Excel into your temp table.

 

[LisaAtWork]

I now understand that it requires entry of the master table information (order) first, then adding the detail level records (orderdetail) after.

My thanks for the assistance.

 

[jdraw]

You are welcome. Good luck with your project.