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.
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.