Appending multiple rows into one (Shopping Cart data)

nickjones87

New member
Local time
Today, 23:30
Joined
Oct 21, 2009
Messages
6
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!! :D
 

Attachments

  • dd.jpg
    dd.jpg
    102.6 KB · Views: 97
Your data structure is not properly optimised. This is always the case when you have fieldnames like ProductName1, ProductName2 etc.
 
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..
 
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.
 
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? :)
 
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.
 

Users who are viewing this thread

Back
Top Bottom