Query Get Latest Date

[MattBaldry]

Hi All,

I have what should be a simple query, but I cannot get it to work right.

I have a table with a DrawingRevisionID (PK, AutoNumber) and DrawingRevisionDate. I want to be able to only show the latest date, but also need to show the PK which may not be the latest number.

As an example I have the below data.

DrawingRevisionID	DrawingID	EmployeeID	DrawingRevision	DrawingRevisionDate	DrawingFile	DrawingNotes
42​	63​	66​	2	25/07/2023​	70-75-1119(2).pdf
43​	63​	5​	1	28/01/2009​	70-75-1119(1).pdf

As you can see, Revision 2 is the latest, but was added before Revision 1. How can I filter the query to only show the highest date?

If I select MaxofDrawingRevisionDate, it still shows the other line as it has GroupBy on the other fields (which may all be different)

How do I Max on 1 field and NOT group by on the rest, but only show the latest by date?

Is there something simple I am missing?

~Matt

 

[Gasman]

Dmax() ?

 

[theDBguy]

You could also use a subquery.