last date list not working

[Danny]

My question is similar to Karen's below, except that I
have a list by last date, and it's not working. I have my
ID field and a date field in my query. I asked it to
group by ID and show me the last date. For some IDs it
shows me the last date, but for some IDs it shows me a
date other than the last date. I've tried sorting
Ascending by Date, sorting Descending by Date and taking
the first record, and any other similar change I can think
of, but the sort will not give me reliable data.

Here is one variation of the sequel statement:
SELECT id_num, Last(effdate) AS Last_effdate
FROM table1
GROUP BY id_num;


Here's a sample of the data:
ID EffDate
1 7/20/2002
1 5/22/2003
2 6/14/2002
2 9/6/2002
2 12/3/2002
3 2/19/2001
3 4/14/2003


And the Results:
1 5/22/2003 - ok
2 12/3/2002 - ok
3 2/19/2001 - WRONG

Any help you can give me in troublshooting this problem
will be greatly appreciated.

Thanks,
Danny N.

 

[Tom Ellison]

Dear Danny:

Change Last() to Max(). Last() is an aggregate function whose definition is
generally meaningless. It practically means "pick a row at random". Same
thing for First().