Query to Select most recent date for each unit.

[Russell]

Im sure this is easy for someone, but SQL not my thing and been struggling for too long to get the syntax right to get this to work.



Table: tblDutyList
Fields:
DutyID (Key)
DutyStation
DutyDate

Table collects multiple entries for all Stations, never more than one on a given date.

I need a query to give me the most recent date from each station.

I can get DISTINCT Stations, and I can sort by date - but cant get those things working together.

Thanks.

[June7]

If you only want DutyStation and DutyDate info, that is simple.

SELECT DutyStation, Max(DutyDate) AS DD FROM tblDutyList GROUP BY DutyStation;

Let the query designer get the syntax for you and you don't really need to know SQL. Study how to build aggregate queries. https://www.teachucomp.com/aggregate...0that%20order.

If you want the entire record that has the latest date, that gets complicated.

[ArviLaanemets]

To get entire records for all stations is not very complicated, but you can't use designer for this - you have to enter the query manually. Something like

SELECT tl.*
FROM tblDutyList tl INNER JOIN (SELECT DutyStation, Max(DutyDate) AS DutyDate FROM tblDutyList GROUP BY DutyStation) LastDuty ON LastDuty.DutyStation = tl.DutyStation AND LastDuty.DutyDate = tl.DutyDate

[isladogs]

To get the latest record per group you either need a 2 step process (stacked query) or a subquery (as shown in post #3)

For more details on this type of subquery, see http://allenbrowne.com/subquery-01.html#TopN

[June7]

Can use query designer to build 'stacked query'. The 2 steps are:

1. build and save inner aggregate query

2. build query that joins table and query objects

After building with designer, switch to SQL View to see and learn syntax.