Access SQL Query: Find the Most Recent Date Entry for Each Employee for Each Training Course

Access SQL Query: Find the most recent date entry for each employee for each training course

The following query should return the result you desire:

SELECT t1.*
FROM 
    Training t1 
    INNER JOIN 
    (
        SELECT [Employee ID], [Course ID], MAX([Course Date]) AS MaxDate 
        FROM Training 
        GROUP BY [Employee ID], [Course ID]
    ) t2 
        ON t1.[Employee ID]=t2.[Employee ID] 
            AND t1.[Course ID]=t2.[Course ID] 
            AND t1.[Course Date]=t2.MaxDate

Access SQL Query: Find the most recent date entry for each player's test date with 2 joined tables

Assuming that there is not more than one test for a player on any one date. Warning, air code. Create a new query qryLastTestDate:
SELECT PlayerID, Max(TestDate) as LastTestDate FROM tblplayerlogistics Group By PlayerID Create a second query qryLastTest:
SELECT tblplayerlogistics.PlayerID, tblplayerlogistics.TestDate, tblplayerlogistics.height, tblplayerlogistics.weight FROM tblplayerlogistics INNER JOIN qryLastTestDate ON tblplayerlogistics.PlayerID = qryLastTestDate.PlayerID and tblplayerlogistics.TestDate = qryLastTestDate.LastTestDate
Your final query would be:
SELECT qryPlayersExtended.PlayerID, qryPlayersExtended.[Player Name], qryLastTest.TestDate, qryLastTest.Height, qryLastTest.Weight FROM qryPlayersExtended INNER JOIN qryLastTest ON qryPlayersExtended.PlayerID = qryLastTest.PlayerID; Add in additional fields as needed.

Access SQL query: find the row with the most recent date for each distinct entry in a table

If you need both the date and the value, you need to do a join:

SELECT ID, Value,As_of 
from yourTable a inner join 
          (SELECT ID, MAX(As_of) as As_of 
          from yourTable group by ID) b 
on a.ID=b.ID and a.As_of = b.As_of

Only show the latest entry for each employee in a list form

Use following SQL to show latest entry for each employee.

SELECT tblTraining.*
FROM tblTraining
WHERE (tblTraining.TrainingDate)=(select max(t2.TrainingDate)
                from tblTraining as t2
                where t2.EmployeeName = tblTraining.EmployeeName);

Then you can use this query as data source of subform.

Access - SQL Query - find most recent record and save it to a table

SELECT TOP 1 * 
FROM tblHistory a
INNER JOIN 
   (SELECT Unit Number, Max([Date]) as MaxDate 
    FROM tblHistory
    GROUP BY Unit Number) b
on a.[Unit Number] = b.[Unit Number]
and a.[Date] = b.MaxDate

You have to bracket the field called Date because Date() is a reserved function and Access will think you're trying to use the function instead of calling the field in your table. In the future, always try to avoid using reserved words as field names, it will save you a lot of headaches.

MS Access Select the most recent date for each unique Id

Try using the Max function whilst grouping by Product Id

SELECT Product ID, MAX(Date Of Purchase)
FROM [table]
GROUP BY Product ID

---

Related Topics