Copy data from one table to another

[leigh]

I am new to using Access and was wondering whether anyone can help me with VBA code which copies data from one table to another.

I have 3 tables:
- Order
- Order_Line
- Import_Order_Lines

I have got a macro working which imports records from CSV into the Import_Order_Lines table. The problem with this data is that the OrderDate and all customer delivery address details are repeated for each order line, therefore I want to split the data up into Order and OrderLine tables.

Each record in the Import_Order_Lines needs the following data copied into the Order_Line table:
- OrderNo
- ProductID
- Qty
- Price

The following data needs to be copied from Import_Order_Lines to Order
- OrderNo
- OrderDate
- CustomerName
- CustomerDeliveryAddress
- CustomerSuburb
- CustomerState
- CustomerPostCode

How can this be done in VB using SQL?

 

[jardiamj]

I think your code should look something like this:

Dim srtQry1 As String, srtQry2 As String

strQry1 = "INSERT INTO Order_Line (OrderNo, ProductID, Qty, Price) " & _
              "SELECT OrderNo, ProductID, Qty, Price " & _
              "FROM Import_Order_Lines;"

strQry2 = "INSERT INTO Order (OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode) " & _
              "SELECT OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode " & _
              "FROM Import_Order_Lines;"

DoCmd.RunSQL strQry1
DoCmd.RunSQL strQry2

 

[Rabbie]

You should have at least 3 tables for this. One for customer details, one for each order and one for each orderline.

You shold not store the same data in more than one place in a relational database.

Jardiamj's code shows you how to do this but remember you first need to make sure the customer record is stored before the Order record before the Orderdetail(Order line data). You also need to ensure you don't store the data for a customer more than once and similarly the Order record should only be stored once for each order.

 

[jardiamj]

Hi again!
Rabbie is right, I didn't pay so much attention to that, but yes! the Customer details shouldn't be stored in the Order table but in a separate table linking to the Order table by a unique ID (Primary key). And I'm not sure if the order you populate the tables matters but I would follow Rabbie's advice and populate first Customers and then Order and then Order_Line.
My code shows how to populate the tables but for the Customers it seems that you don't have a Unique ID, and I wouldn't use the CustomerName as so, instead I would create one, it could be a autoincrementer numerical value, I don't like autoincrementer values though; but it may be the easiest way.
I'm sorry I didn't pay attention to that before and just jumped into the code...lol
Cheers!