Arne Clicteur Arne Clicteur - 1 year ago 98
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
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 Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download