Appending multiple rows into one (Shopping Cart data)

[nickjones87]

Hi,

I'm importing orders into Access 2002, the csv file is from eBay. I've stumbled into a problem with orders with 2 or more different products for each sale.

I've attached a picture so it's easier to understand my problem.
As you can see, rows 3-5 and 20-25 contain multiple products.
Once this file is imported into access, each row gets its own record, what I want is for orders like this to be appended to just one record in access

EG, I have fields Product Name 1, Qty1, Product Name 2, Qty2, Product Name 3, Qty3, etc in my database, so ...

row 4 "Item Title" & "Quantity" in the screenshot would be appended to "Product Name 1" & "Qty1" in my database on the same record.
row 5 "Item Title" & "Quantity" in the screenshot would be appended to "Product Name 2" & "Qty2" in my database on the same record.

And so on....
The only unique identifier is the first column in the screenshot "Sales Record", rows 3 to 5 and 20 to 25 have the same Sales Record.

Is there a simple way of doing this?

Thanks guys!!

 

[Galaxiom]

Your data structure is not properly optimised. This is always the case when you have fieldnames like ProductName1, ProductName2 etc.

 

[nickjones87]

Your data structure is not properly optimised. This is always the case when you have fieldnames like ProductName1, ProductName2 etc.

Hi,

What would you suggest?

Thanks..

 

[Galaxiom]

Normally you would have a table with the information about the transaction with a TransactionID field as a number.

The items are held in another table as individual records with a field (the Foreign Key) holding the TransactionID that each records belongs with.

 

[nickjones87]

Normally you would have a table with the information about the transaction with a TransactionID field as a number.

The items are held in another table as individual records with a field (the Foreign Key) holding the TransactionID that each records belongs with.

If I understand correctly, sounds a little complicated as the whole database and forms have been designed around 1 table holding all data (I'm an amateur)

Any advice on a solution to the problem, whilst keeping the 1 table?

 

[Galaxiom]

The way you have designed your database is a very common beginner's mistake.

Unfortunately while you persist with that flat table structure you will continue to encounter difficulties. Continuing like this is simply delaying the inevitable.