Full Version: Sorting with an aggregate function
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
My table has name and date fields. Both are text fields. There can be multiple dates for a name. I need to write a query to return the most recent date for each name, with any null value date counting as the highest date.

I managed to sort the data in the table by date, with null values first, using 2 queries:

SELECT * from myTable;
SELECT * FROM myQuery ORDER BY IIf([Date] Is Null,99999,Val([Date])) DESC;

Can I do something similar to get just the most recent date for each name, with any null values as the most recent date, using an aggregate or other function?
I don't see what your first query adds to the process.

This query gives you the initial answer.
SELECT namefield, max( IIf([Date] Is Null,99999,clng([Date]))) as Seq FROM mytable Group BY namefield
Further refine the query by using the Nz() function instead of the IIf(), and don't use Date as the name for a field it is a Reserved word and will cause you troubles.

SELECT namefield, Max(Nz(dtmDate,Date()) as Seq FROM mytable Group BY namefield
Thank you both.

You are welcome.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.