Excel data into 2 different tables, going into Access.

[oddish]

solved- thank you june7

[June7]

The table doesn't have date or customer, I assume those are the two columns you deleted. The 'many' records need to be rearranged like this (example is sorted by OrderID):

OrderID | Quantity | Product
1 | 1 | B&BDOUGF
1 | 25 | CUTDOUGF35
1 | 1 | HOW4HONDGENG
...etc

I suggest the quickest method for this number of records is copy/paste. Copy and paste each ProductID and Quantity column pair below the first pair. Also copy the OrderID column with each pair. Do a sort to make rows with blank Product/Quantity list at the end and delete those rows (or don't and delete after the import). Then import to OrderDetails table.

A side note: It is possible to select spreadsheet cells and then copy/paste to Access table. A little tricky but doable. The reverse is also possible and even easier.

You might want to verify that the Autonumber id generated for the Orders records coincide with the original ID numbers in the spreadsheet

[oddish]

thanks for your response, but in my opinion that would take forever with the copy and paste method. Because there is about over 1k entries, but yes you had the right idea.

I've experimented a bit last night, and I've come to the conclusion that making a macro to organize the data to the structure of your example table would have been the best solution:

https://sites.google.com/a/madrocket...ows-to-columns

The macro(s) there are the ones that best fit my solution if anyone is wondering.

So this thread can be closed, thanks for your response.

[June7]

My intended suggestion was to copy/paste whole columns. 1000 rows for each of the 9 column pairs is not much. Would have taken 10 minutes, less time than building a macro from scratch or in your case, less time than searching for a macro. My example was showing rows sorted after the columns copy/pasted. But the macro was a good exercise in using code so congratulations on finding solution.

Really should not edit original post to remove question. It can make the following posts difficult to understand for other readers exploring a similar issue.