Grouped sorting, with a "first" on a date column does work.

Hi All,

I have inherited a database that is giving me fits. I've fixed lots
of weird errors the previous person made, but have come across a
problem I can't figure out.

I've been asked to add a new report that shows the most recent entry
for each item type.

This is a typical grouping query where I sort by the type, then the
DESC date, and pick the First ID# from each group. I do this
regularly in other databases.

My ID column is called "uniq#"
My Type column is called "Alloy"
My Date columne is called "date entered".

So, here is the SQL on the current table I'm using...

SELECT [Alloy Sheet].Alloy, First([Alloy Sheet].[date entered]) AS
[FirstOfdate entered], First([Alloy Sheet].[uniq#]) AS [FirstOfuniq#]
FROM [Alloy Sheet]
GROUP BY [Alloy Sheet].Alloy
ORDER BY [Alloy Sheet].Alloy, First([Alloy Sheet].[date entered])
DESC;

The problem, is this ends up picking a middle (random?) value of the
dates. If I switch "first" to "max", it gives me the max date, but
then I have no way of selecting the uniq#.

(I actually have more grouping columns in my real application, but
even this reduced table has the same behavior.)

I've recreated this table, and entered the same information in new,
and it doesn't work either.
----------------------------------------------
I don't know what to do. I don't know if there is some sort of Index
setting that's screwed things up, or what.

I made a test table that was just...


Can someone help.

If I have this data....
auto date alloy
1 3/1/2008 1
2 2/1/2008 1
3 1/1/2008 2
4 2/1/2008 2
5 3/1/2008 2
6 1/1/2008 1

I WANT to retrieve the ID's of the most current Alloy entries like
this....
auto date alloy
1 3/1/2008 1
5 3/1/2008 2

So, I figure, Grouping query. Sort by Alloy ASC first, Sort by Date
DESC second, and pick "first" date, and "first" auto number. The SQL
generated is like this...

SELECT Table1Test.alloy, First(Table1Test.date) AS FirstOfdate, First
(Table1Test.auto) AS FirstOfauto
FROM Table1Test
GROUP BY Table1Test.alloy
ORDER BY Table1Test.alloy, First(Table1Test.date) DESC;

Instead, is returning this...
auto date alloy
1 3/1/2008 1
3 1/1/2008 2

I've tried doing a "pre-query" that does the sort first, but that
seems to give the same result. If I go for "max" date, then the Auto
number isn't reported right.

Can anyone suggest a way of doing this?

TIA,

Jon