Select ONLY Max(date) when there's repeating dates

by DrD4rk   Last Updated May 15, 2019 14:06 PM

I'm using Acces 2016

Here is a sample of my table

TRACKING

tracking_id   date_action  action_id    staffing_id
------------- -----------  -----------  -----------
 1             2019/03/04  4             2
 2             2019/03/04  3             2
 7             2018/10/25  67            3
 4             2018/10/25  8             3
 5             2019/05/05  10            5
 6             2019/05/05  6             5
 8             2019/03/02  55            6

The Result I want is the following

  tracking_id   date_action  action_id    staffing_id
    ------------- -----------  -----------  -----------
     2             2019/03/04  3             2
     4             2018/10/25  8             3
     6             2019/05/05  6             5
     8             2019/03/02  55            6

I need to find the max(date_action_taken) group by staffing_id and join it with other tables to get the fields in the Action table using action_id and all of the fields in Staffing table using the staffing_id

I first try to get the the max date in the Tracking table for each staffing_id

SELECT staffing_id, Max(Tracking.date_action_taken) AS MaxOfdate
FROM Tracking
GROUP BY staffing_id

When I join the query above back to the same table to get the other fields of the Tracking table. It still returns the max(date_action) but it also returns the repeated ones. The query I used is below:

Select  tracking_id, t2.mxdate, t1.action_id, t1.staffing_id
FROM Tracking t1
inner join
(select  max(date_action) as mxdate, staffing_id
From Tracking
Group by staffing_id
) t2
on t1.staffing_id = t2.staffing_id and t1.date_action_taken = t2.mxdate

I even tried another query to get the max(tracking_id) instead using this query

Select Tracking.*
From Tracking 
Where tracking_id IN
(Select Max(tracking_id) as t_id
From Tracking
Group by staffing_id
)

This works great until I realize that the max(tracking) is not necessarily the max(date_action).

I even used DISTINCT. And it still returns the repeated max(date_action).

Select  DISTINCT tracking_id, t2.mxdate, t1.action_id, t1.comment, t1.staffing_id
FROM Tracking t1
inner join
(select  DISTINCT max(date_action_taken) as mxdate, staffing_id
From Tracking
Group by staffing_id
) t2
on t1.staffing_id = t2.staffing_id and t1.date_action_taken = t2.mxdate

I am not sure what to do. Is there a way, I can first get the max(date_action) by staffing_id and if two of them are repeated to get the max(tracking_id) between the max(date_action).

I am thinking of adding another field to act as a flag. A data type yes/no, field named (isTheLastAction) so on each insert in the tracking table. It find the previous isTheLastAction, put it at false, and the new insert to true within the same staffing_id that way, it doesn't depend on the date_action nor the tracking_id. I am just not sure how to do that.

Any thoughts on either helping me solve the max(date_action) or using the flag on insert?



Related Questions


Updated May 16, 2019 13:06 PM

Updated June 05, 2019 18:06 PM

Updated March 16, 2019 04:06 AM

Updated March 25, 2019 13:06 PM