Find the latest record-CodePudding

In MS Access I have the DateList table, which holds the due date of different orders. Thus, the table has two columns: OrderNo and DueDate. For some order numbers, there could be multiple DueDates. The table could look like below:

OrderNo DueDate
100     12/9/2021
101     20/9/2021
102     30/9/2021
100     7/10/2021
102     11/10/2021
103     15/10/2021
…

My goal is write a query to fetch the latest DueDate of each OrderNr.

I created two queries; the first one, qry1, to generate a list of OrdNo without duplications:

SELECT  
DateList.OrderNo AS UniqOrderNo 
FROM DateList 
GROUPBY DateList.OrderNo;

in the second query, qry2, I used the DMax function in order to search through DueDates of each order for the maximum value.

SELECT 
qry1.UniqOrderNo
,DMax("[DueDate]","[DateList]","[OrderNo]='[qry1]![UniqOrderNo]'") AS LatDuDate
FROM qry1 
INNER JOIN DateList 
ON qry1.UniqOrderNo = DateList.OrderNo;

LatDuDate represents the latest DueDate of the Order.

The query is unfortunately does not work and returns nothing.

Now my questions:

Is there something wrong with my approach / queries?

Is there better way to accomplish this task in MS Access?

CodePudding user response：

You almost figured it out yourself. Max returns you the biggest value of the group.

SELECT Max(DueDate) DueDate, OrderNo
FROM DateList
GROUP BY OrderNo

CodePudding user response：

Similar to Christian's answer, but since OrderNo is a unique id, you can simply select the First() instead of grouping - it performs better. **

Of course it depends on the number of records the table holds.

SELECT First(OrderNo) AS OrderNo, Max(DueDate) AS DueDate
FROM DateList;

** Source: Allen Browne - Optimizing queries