How to from 1*apple,1*orange,1*apple to 2*apple,1*orange

Lets say there is a data table called ShoppingBasket. It has fields like "product", "price", "quantity" and "total" and so on. It is possible to have duplicates, but I think it is better to run a query and remove them. And update quantities. In the end, I plan to calculate total price.

For total price there seems to be a field or property "Formula" in column properties.

As this is really two questions, I take any help.

Leif

We need to see how your data looks like and also how you want it updated..

|||

"We need to see how your data looks like and also how you want it updated.." Here it comes.

I have a table called t_shopping_basket. There the user inserts items from products table "t_Tuote". I use this query:

"INSERT dbo.t_shopping_basket (Product_code, Name,Model,Quantity,Price,Alv) SELECT Tuotekoodi,Name,Model,Toimittajanimi,@.Quantity,Price,Alv FROM dbo.t_Tuote WHERE Product_code=@.Product_code", conn) ".

I have included also a gridView of "shopping basket" Its query is like:

"UPDATE [t_shopping_basket] SET [Product_code] = @.Product_code,[Quantity] = @.Quantity,[Name] = @.Name WHERE [Product_code] = @.Product_code"

Nothing prevents users pressing buy several times, so same item can be there in many places. If I leave them there, it has its good positive and negative sides. I probably should put an extra field like "item index" or so into the table then. Or I could leave quantity field out. 3 pieces means 3 rows of something.

Best way to my mind is to delete or prevent duplicates. But then, I have to find those duplicates first, and when I delete a record, I must increase item quantity. This feels like a complicated thing to do, especially with query.

How have others done this? What could my query look like?

Regards

Leif