Find most recent date in a query

[Design by Sue]

Can someone please tell me the correct code to add in a query to limit the results to the most recent date only. I have searched but can't seem to find the answer to what I would think is something that would be commonly used. Thanks!

 

[plog]

Pretty ambiguous question.

Most recent date in life (which means today) : you use Date() in the criteria under the date field
Most recent date of the datasource : you use DMax() in the criteria under the date field
Most recent date of a group of records : you build a subquery to find the most recent date then use that subquery in a further query

Perhaps you can provide sample data as an example of what you want.

 

[June7]

If you only want the date, that can be done with an aggregate: SELECT Max(datefield) AS MaxDt FROM tablename

If you want entire record:
SELECT TOP 1 tablename.* FROM tablename ORDER BY datefield DESC;
If multiple records have the same date, they will all return.

 

[Design by Sue]

Pretty ambiguous question.

Most recent date in life (which means today) : you use Date() in the criteria under the date field
Most recent date of the datasource : you use DMax() in the criteria under the date field
Most recent date of a group of records : you build a subquery to find the most recent date then use that subquery in a further query

Perhaps you can provide sample data as an example of what you want.

"Most recent date of a group of records : you build a subquery to find the most recent date then use that subquery in a further query"

That is exactly what I am trying to do. I tried creating it using Max but as stated that shows only the most recent and not all of the records. DMax sounds like what I need - can you tell me how to apply that to the subquery please?

 

[plog]

Not without sample data. Provide 2 sets:

A. Starting data. Include table and field names and enough sample data to demonstrate all cases.

B. Expected results of A. Provide me what data you expect to end up with based on A.

 

[Design by Sue]

I have saved a temporary table and query with the info required but how do I get them from access to upload?

 

[Design by Sue]

I think I have it - here is a database with the query that I need to sort and a table with info that should be enough to work with. Thanks as always!

 

[June7]

Did you try suggestion in post 3?

 

[Design by Sue]

I think I have it - here is a database with the query that I need to sort and a table with info that should be enough to work with. Thanks as always!

I am sorry - the results I hope to get are a listing of all training completed by the employee but only the most recent one - the data has a couple of classes that the employee has completed 2 times and there are others that they have only completed once. All of the training needs to be listed but limited to the most recent when there are more than one.

 

[Design by Sue]

Did you try suggestion in post 3?

I tried max but that only gave me the most current of all of the training not limited to the most current for the ones with more than one date and also including the other training not completed on that day

 

[plog]

A quarter of the way there. I asked for 2 sets data:

A--starting data. Is the table or the query the starting data?

B--expected results. Please show me the data you expect to end up with.

 

[MajP]

This is an aggregate query. You want the Max date, by employee, by training.

SELECT [Main TBLTemp].[Employee Number], [Main TBLTemp].Description, Max([Main TBLTemp].DateCompleted) AS LastCompleted
FROM [Main TBLTemp]
GROUP BY [Main TBLTemp].[Employee Number], [Main TBLTemp].Description
ORDER BY [Main TBLTemp].[Employee Number], [Main TBLTemp].Description;

Employee Number	Description	LastCompleted
5644	General Acceptance Test For Appearance (60047645.009)	7/18/2024
5644	GOOD MANUFACTURING PROCEDURE	1/26/2016
5644	Hazadous Communication/Contigency Plan	1/26/2016
5644	Label Room Instruction.	1/6/2012
5644	Physical Inventory	7/13/2024
5644	Sanitary procedure	5/28/2013

qryMaxDate

​

 

[Design by Sue]

Re-uploading database - there was an error in the table and including a screenshot showing the results I need to get.

Table is the starting data (I think that is the correct answer to that question - the query needs to show the results in the excel file)

 

[Design by Sue]

This is an aggregate query. You want the Max date, by employee, by training.

SELECT [Main TBLTemp].[Employee Number], [Main TBLTemp].Description, Max([Main TBLTemp].DateCompleted) AS LastCompleted
FROM [Main TBLTemp]
GROUP BY [Main TBLTemp].[Employee Number], [Main TBLTemp].Description
ORDER BY [Main TBLTemp].[Employee Number], [Main TBLTemp].Description;

Employee Number	Description	LastCompleted
5644	General Acceptance Test For Appearance (60047645.009)	7/18/2024
5644	GOOD MANUFACTURING PROCEDURE	1/26/2016
5644	Hazadous Communication/Contigency Plan	1/26/2016
5644	Label Room Instruction.	1/6/2012
5644	Physical Inventory	7/13/2024
5644	Sanitary procedure	5/28/2013

qryMaxDate

​

Thanks I think that is is! I will see how to apply that now.

 

[MajP]

SQL MAX() function with GROUP by, ORDER by - w3resource

SQL MAX() function with GROUP by, ORDER by: How the GROUP BY and ORDER BY clause along with the SQL MAX() can be used to find the maximum value of a column over each group.

www.w3resource.com

 

[MajP]

FYI, if this is a database that you are building the table needs a lot of work.

Employee information should be in one table (entered once for each employee)
Course information in another table (entered once for each course)
Then you need a junction table to relate Employees to Courses.

tblEmployeeTraining
-- EmployeeID_FK (relates to employee table)
-- CourseID_FK (relates to a course)
-- DateCompleted

You should never be repeating data like first and last in the table and SOP versions.
Also get rid of spaces in any name. You can use underscores.

 

[Design by Sue]

This is an aggregate query. You want the Max date, by employee, by training.

SELECT [Main TBLTemp].[Employee Number], [Main TBLTemp].Description, Max([Main TBLTemp].DateCompleted) AS LastCompleted
FROM [Main TBLTemp]
GROUP BY [Main TBLTemp].[Employee Number], [Main TBLTemp].Description
ORDER BY [Main TBLTemp].[Employee Number], [Main TBLTemp].Description;

Employee Number	Description	LastCompleted
5644	General Acceptance Test For Appearance (60047645.009)	7/18/2024
5644	GOOD MANUFACTURING PROCEDURE	1/26/2016
5644	Hazadous Communication/Contigency Plan	1/26/2016
5644	Label Room Instruction.	1/6/2012
5644	Physical Inventory	7/13/2024
5644	Sanitary procedure	5/28/2013

qryMaxDate

​

A million thank yous to you!!! I would not have been able to figure that out.