You don't need SELECT TOP 1.  Just a regular aggregate query will do.  You use GROUP BY on the SKU only, so each SKU will appear only once in the result.  Then you use the Max() function on DateDelivered.  This will return the max date for each SKU, exactly what you want.  You may need to use a function on UnitCost too.  If the same style was sold on the same max date with two different prices, then which price do you want to show?  You may use Max(), First(), Min(), etc. on the price.  Access requires this to be used even if you only have one price or N prices on the same max date.

See picture below.  I used my own tables in the example, but you can easily adapt this to your tables (my style number is your SKU).  You can even add a sort order for the SKU, as I did below.  The result will show each style only once, with each date being the max (most recent) date for that style.

Many thanks, Kevin. I will give that a go.

Having carried out some testing, I can report that your solution works for me, if Last() is used as the function for UnitPrice. I'm very grateful for your help.

Turns out I was wrong. The solution doesn't work, sadly. We have thousands and thousands of SKUs and hundreds and hundreds of purchase orders, so it took me a while to do enough testing. Unfortunately I discovered several examples of incorrect results.

If anyone else has any suggestions, I should be glad to hear them, but I fear it's back to the drawing board.

It doesn't matter if you have thousands or just a few records, the method I mentioned should give you an objectively true result, because that's how an aggregate query works.  When you use Group By on the SKUs and use Max() on the dates, it means in a group of *identical* SKUs, the max (most recent) date for that group is so and so.  So that date must exist for that SKU, and it is the most recent date as well, which is exactly what you try to find.  If you see any result that you think indicates otherwise, please post some screenshots.  Does the query give you a date that does not exist for that SKU, or isn't the most recent date for that SKU?

It's strange. The problem isn't the dates because, as far as I can tell, the query always shows the "correct" most-recent date. The problem is that in some cases the price displayed is not the price on the purchase order from the date in question, while in other cases it is. In other words, the combination of "GroupBy SKU" and "Max() date" works as it should and produces accurate results. The problem comes when I throw a third field (i.e. price) into the mix. Doing that produces the inconsistent results mentioned above.

Did you add any extra columns other than SKU, date, and price to the query?  If you did, that would cause the mismatching data you described.  The aggregate query in question can only work with SKU (as Group By) and date and price as aggregates.  It can't have any other columns.  That's aggregate queries by nature: they can only have Group By columns and aggregate columns.

If you must see data from other columns that actually come from matching records, I suggest you replace the price field in the query with the Order ID field (which uniquely identifies an order).  The query will come something like:

    SELECT SKU, Max(Order_Date) As MaxDate, First(OrderID) As OrderID
    FROM OrdersT
    GROUP BY SKU;

Call this query Q1, or something.  Will an unique OrderID, you can use it pull in extra columns from the order table; and you can be sure that only matching records are retrieved, because that's what unique primary ID can do.  Create a second query, a regular Select query to join Q1 with the order table:

    SELECT Q1.* OrdersT.Price, OrdersT.OtherFields
    FROM Q1 INNER JOIN OrdersT
    ON Q1.OrderID = OrdersT.OrderID

Thanks, Kevin. I'd already come to the conclusion that a second query was necessary. I'll give this a try.

The suggestion of creating two queries was the solution, although I had to change the SQL of query 1. The issue with that query was the second aggregate "column". Max(Order_Date) worked fine, but First(OrderID) displayed the first order ID for the SKU, rather the order ID associated with the purchase order on Max(Order_Date), if you see what I mean.

I'll post the SQL here, in case anyone else finds it useful. There are two queries: Q1 and Q2. The second query produces the desired results: a recordset showing every SKU, together with the most recent date the product was purchased, the unit cost on that occasion and the ID of the purchase order in question.


Q1:
SELECT PurchaseOrderDetailT.SKU, Max(PurchaseOrderT.DateDelivered) AS MaxDate
FROM PurchaseOrderT INNER JOIN PurchaseOrderDetailT ON PurchaseOrderT.POID = PurchaseOrderDetailT.POID
GROUP BY PurchaseOrderDetailT.SKU;


Q2:
SELECT Q1.SKU, Q1.MaxDate, PurchaseOrderDetailT.UnitCost, PurchaseOrderT.POID
FROM Q1 INNER JOIN (PurchaseOrderT INNER JOIN PurchaseOrderDetailT ON PurchaseOrderT.POID = PurchaseOrderDetailT.POID) ON (Q1.MaxDate = PurchaseOrderT.DateDelivered) AND (Q1.SKU = PurchaseOrderDetailT.SKU)
ORDER BY Q1.SKU;

Thanks again for your help and suggestions!

You're welcome.  Just want to add that if the order date has time info too, it will give you clear-cut results because each order will likely have a unique order date/time (unless you can enter multiple orders within a millisecond).  If there is no time info, then each date can have multiple orders, which will complicate things.