Arne Clicteur Arne Clicteur - 3 months ago 8
SQL Question

Access: Find partial duplicates, show only one and sum a value

I have a database for my invoices. I would like to make a form where I list all purchases from one customer. If the customer bought the same item more than once I'd like to show that item only once and sum the quantity field. I'd like to find an SQL solution, but I'm a bit stuck.

This is the query in my form:

SELECT Order.fkCustID, Order.OrderID, OrderLine.fkProductID, OrderLine.Qty, ProductList.Item
FROM ProductList INNER JOIN
([Order] INNER JOIN
OrderLine
ON Order.OrderID = OrderLine.fkOrderID
) ON ProductList.ProductID = OrderLine.fkProductID
ORDER BY ProductList.Item;


The OrderLine table contains the individual products bought, the Order table links these to my customer table. ProductList contains the product description.

So where fkCustID and fkProductID are the same, I'd like to only show the oldest entry and sum the Qty field of both.

Answer

I think you just want an aggregation query, that looks something like this:

SELECT ol.fkProductID, SUM(ol.Qty) as qty, pl.Item
FROM ProductList as pl INNER JOIN
     ([Order] as o INNER JOIN
      OrderLine as ol
      ON o.OrderID = ol.fkOrderID
     )
     ON pl.ProductID = ol.fkProductID
WHERE o.fkCustID = ???
GROUP BY ol.fkProductID, pl.Item
ORDER BY pl.Item;