Most Recent date up to 1 year ago.

[nigelbloomy]

I have a list of part numbers, the dates they were ordered, and the prices. For each part I need to find the most recent price we paid up to 6/1/2014. So if there are prices after that date I want to ignore them.

I have used a double query like this one http://www.baldyweb.com/LastValue.htm to figure out the most recent date for each item. It works when I want to include all of the data up to today. I tried to limit the date criteria to be before 6/1/2014, but then I only get a list of the items where the max date only exists before 6/1/2014. Items that have dates before and after that time simply don't show up. I understand how that can make sense to the computer, but how do I change it so I get the most recent date up to 6/1/2014?

In the example below I am looking for the $25 price because it is before 6/1/2014 even though the part number does have a price after that date as well.

Part number/Date/Price


1/ 8/1/2014/ $20.00
1/ 5/1/2014/ $25.00

[nigelbloomy]

I figured it out. I had to make a third query.
The first query filters out all of the dates before 6/1/2014.
The second query finds the max date for each part number from the results of the first query.
The third query matches the transaction dates back to the original data and tells me the price for the most recent date before 6/1/2014.

That just seems more confusing than it should be. But it does work.

[orange]

You could do it with 2 queries as Paul's sample showed.

First query (query51)

SELECT TestProducts.Prodnumber
    ,Max(TestProducts.PurchaseDate) AS MaxPurchaseDate
FROM TestProducts
WHERE (((TestProducts.PurchaseDate) < #6/1/2014#))
GROUP BY TestProducts.Prodnumber;

Second query:

SELECT TestProducts.Prodnumber
    ,TestProducts.PurchaseDate
    ,TestProducts.PurchasePrice
FROM TestProducts
INNER JOIN Query51 ON 
(TestProducts.PurchaseDate = Query51.MaxPurchaseDate)
    AND 
(TestProducts.Prodnumber = Query51.Prodnumber);

[nigelbloomy]

@Orange, When I tried that first query, it only gave me items where the most recent purchase date was before 6/1/2014. An item such as my example would not show up because the max date was after 6/1/2014.

[orange]

I just reread your post. You wanted prices up to 6/1/2014. (price for Max purchaseDate before 6/1/2014)

What exactly is the issue??

My query51 finds the Max date for each ProductNumber and the PurchaseDate are before 6/1/2014.
The second query gets all field values based on the Date field and productNumber in query51.

[nigelbloomy]

I see the difference now. The first query I was using is below:

SELECT TestProducts.ProdNumber, Max(TestProducts.PurchaseDate) AS MaxOfPurchaseDate
FROM TestProducts
GROUP BY TestProducts.ProdNumber
HAVING (((Max(TestProducts.PurchaseDate))<#6/1/2014#));

The way I was entering things into the design view I ended up with a "Having" statement at the end. Yours has a "Where" statement. Mine won't find any results for the example above. Your works just like I wanted it to.

Thank you. I am happy to be able to do it with one less query.

[orange]

The HAVING clause acts (takes effect) after the aggregation.
The WHERE clause acts on each record (before aggregation).

Good luck with your project.