Max ( ) - use to return latest date - or is there another way?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is a baby question but altho I got it to work in Access 97 I have both
lost my notes and can't get it to work in Access 2003
A query gives me a list of names and dates:
Name Date
James 10/10/10
James 11/10/10
James 12/10/10
James 13/10/10
Fred 20/10/10
Fred 21/10/10
But what I need is just the name and lastest date for each of these names...
It is probably very simple...
 
SELECT Name, Max(Date)
FROM MyTable
GROUP BY Name

BTW, hopefully Name and Date aren't your actual field names. Both are
reserved words in Access, and you can run into problems using reserved
words.
 
Doulgas - thanks - of course I cannot get it to work...
This is the SQL statement I am using to create the query (called query1)
SELECT Table1.ID, Table1.NameCo, Table1.DateOrd
FROM Table1;
and this gives me:
ID NameCo DateOrd
1 James 10/10/10
2 James 11/10/10
3 James 12/10/10
4 James 13/10/10
5 Fred 20/10/10
6 Fred 21/10/10
and what output I need is
3 James 12/10/10
6 Fred 21/10/10
If I modify the SQL statementment as follows:
SELECT Table1.ID, Table1.NameCo, Table1.DateOrd
FROM Table1,
SELECT NameCo, Max(DateOrd)
FROM Table1
GROUP BY NameCo;
I get SYNTAX ERROR in FROM Clause
I think I am correct in putting a comma to separate the two statements
aren't I?
Jon
 
Create a query to give you the name and maximum date for each name, then
save that query:

SELECT Table1.NameCo, Max(Table1.DateOrd)
FROM Table1
GROUP BY Table1.NameCo;

Next, create a second query that joins Table1 to the query you just saved.

If you're using Access 2000 or newer, you can do this all in one step:

SELECT Table1.Id, Table1.NameCo, Table1.DateOrd
FROM Table1
INNER JOIN
(SELECT Table1.NameCo, Max(Table1.DateOrd) AS MaxOfDateOrd
FROM Table1
GROUP BY Table1.NameCo) AS Max
ON Table1.NameCo=Max.NameCo AND Table1.DateOrd = Max.MaxOfDateOrd
 
Thank you very much indeed - it works perfectly - I'd muddled myself up. I'd
better buy the book! The help files in Office 97 are in fact more helpful
than the online stuff you are now directed to. That's progress :)

Jon
 
Back
Top