Query of Struggling With...

[terbs]

I need a query to return the most recent price of a particular Product from each supplier it has been purchased from.

This involves two tables.

- TBLPurchaseOrder
- TBLPurchaseOrderItem

So I need;

- Max(TBLPurchaseOrder.Date)
- Where (TBLPurchaseOrderItem.ProductID = @ProductID)
- For Earch (TBLPurchaseOrder.SupplierID)

TBLPurchaseOrder.ID = TBLPurchaseOrderItem.ID

Ive tried to get this myself, but I go around in circle. Here is the latest, it only returns the most recent record, not for each supplier

SELECT PO1.Date, PO1.SupplierID, POI1.ProductID
FROM TBLPurchaseOrder AS PO1 INNER JOIN TBLPurchaseOrderItem AS POI1 ON PO1.ID = POI1.ID
WHERE PO1.Date = (SELECT MAX(PO2.Date)
FROM TBLPurchaseOrder AS PO2 INNER JOIN TBLPurchaseOrderItem AS POI2 ON PO2.PONumber = POI2.ID
WHERE PO2.SupplierID = PO1.SupplierID)
AND POI1.PartID=751045;

 

[DCrake]

For each product item purchased there will be a date when this occured, and the date the purchase order was generated. When the goods are received via a goods received note (GRN).

PO Date
Order Confirmation Date
GRN Date

So you want to create a query based on your goods received grouped by supplier id by product id and the Max(GRN Date)

Can you source a specfic product from more than one supplier? If so, you will get the date the product was last received from each supplier.

CodeMaster: