Solved Top values for unique records (2 Viewers)

[mounty76]

Hi All,

I have a table which gets joining and leaving dates for crew, it is something like this:

SeatimeID, CrewID, JoinDate, LeaveDate

The CrewID is linked to a crew table and so therefore the data in the seatime table looks something like this :

1, 120, 1/1/23, 2/1/23
2, 12, 12/1/23, 23/4/23
3, 110, 12/2/22, 23/6/22
4, 120, 4/4/23, 17/6/23
5, 12, 6/8/23, 9/10/23

My question....how do I find the highest SeatimeID for each particular CrewID? I really want the query to return

3, 110, 12/2/22, 23/6/22
4, 120, 4/4/23, 17/6/23
5, 12, 6/8/23, 9/10/23

I've tried a few different ways to achieve this and I cannot seem to get it right?

 

[RogerCooper]

You will need 2 queries. The first query selects the max Seatime_ID for each Crew_ID. The second query links the first query to the original data to pull up the dates.

Queries referencing other queries is a useful technique.

 

[ebs17]

Recognize the analogy:

1 query from 2 table to know the last record by date ?

Hello I have 2 tables TableStartPrice and TableNewPrice In each 3 same fields: Adress, Date, Price In TableStartPrice only one record per adress In TableNewPrice could be several records per adress With a query I want to know the last price (by date) for each adress coming from...

www.access-programmers.co.uk

 

[bob fitz]

Try:

SELECT Max(YourTableName.SeatimeID) AS MaxOfSeatimeID, YourTableName.CrewID, Last(YourTableName.JoinDate) AS LastOfJoinDate, Last(YourTableName.LeaveDate) AS LastOfLeaveDate
FROM YourTableName
GROUP BY YourTableName.CrewID
ORDER BY Max(YourTableName.SeatimeID);

 

[mounty76]

Perfect, thanks Bob, much appreciated

 

[bob fitz]

Perfect, thanks Bob, much appreciated

Glad to help if I can.
Be sure to test well wit more data.