last price paid based on date

  • Thread starter Thread starter Dimitri de Failly
  • Start date Start date
D

Dimitri de Failly

Hello,

I'm trying to do the following
I have a table with "purchase date", "item purchased", "vendor" and "price"
and would like to extract for each item and vendor the LAST price paid.

040101 ItemA VendorX 10$
040131 ItemA VendorX 11$
040215 ItemA VendorY 12$
040312 ItemA VendorY 13$

The result should be
040131 ItemA VendorX 11$
040312 ItemA VendorY 13$

Any thoughts ?
Thanks in advance for your help !!

ps: I have tried to run a pivot table view with a grouping by max but it
shows the last date (in my example 040312)
 
I see two queries.

qry_Purchases_01:
SELECT Max(tblPurchases.[Purchase Date]) AS [Purchase
Date], tblPurchases.[Item Purchased], tblPurchases.Vendor
FROM tblPurchases
GROUP BY tblPurchases.[Item Purchased],
tblPurchases.Vendor;

qry_Purchases:
SELECT tblPurchases.[Purchase Date], tblPurchases.[Item
Purchased], tblPurchases.Vendor, tblPurchases.Price
FROM tblPurchases INNER JOIN qry_Purchases_01 ON
(tblPurchases.Vendor = qry_Purchases_01.Vendor) AND
(tblPurchases.[Item Purchased] = qry_Purchases_01.[Item
Purchased]) AND (tblPurchases.[Purchase Date] =
qry_Purchases_01.[Purchase Date]);

This might just be a formatting issue, but make sure your
Purchase Date field is a Date/Time field and not text.

Mark

-----Original Message-----
Hello,

I'm trying to do the following
I have a table with "purchase date", "item
Click to expand...
purchased", "vendor" and "price"
 
Back
Top