last price paid: query with max in date

[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)

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[Your Table].[item purchased],
[Your Table].[vendor],
[Your Table].[price]
FROM
[Your Table]
WHERE
[Your Table].[purchase date] =
(SELECT
Max([Self].[purchase date])
FROM
[Your Table] AS [Self]
WHERE
[Self].[item purchased] = [Your Table].[item purchased]
AND
[Self].[vendor] = [Your Table].[vendor])

For a discussion of this and other approaches, you might refer to:

http://www.mvps.org/access/queries/qry0020.htm

 

[Tom Ellison]

Dear Dmitri:

This is a case for a correlated subquery. This will find the maximum
PurchaseDate and then retrieve the entire row with that date to find
the Price.

SELECT MAX(PurchaseDate) AS PurchaseDate, ItemPurchased, Vendor,
(SELECT Price FROM YourTable T1
WHERE T1.ItemPurchased = T.ItemPurchased
AND T1.Vendor = T.Vendor
AND T1.PurchaseDate = (SELECT MAX(PurchaseDate)
FROM YourTable T2
WHERE T2.ItemPurchased = T1.ItemPurchased
AND T2.Vendor = T1.Vendor)) AS Price
FROM YourTable T
GROUP BY ItemPurchased, Vendor
ORDER BY ItemPurchased, Vendor

You will need to change the names of columns and tables above to
exactly match your database. Don't forget square brackets around
column names that contain spaces or punctuation characters.

The T, T1, and T2 are aliases and are necessary to allow reference to
3 separate instances of your table. You need not change them, but if
you do, change them systematically.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts