[AccessD] Last Date Query

Dear group,

thanks for all the help.
I have tried all the suggestions.
Also i have added some records to the test-table, to exclude double dates etc.

Here is the test table:

Pat             Date                 Result
A1              1-1-20111            5
A1             10-10-2011            7
A1             11-11-2011            8
A1             11-11-2011            6
B2               4-4-2011            6
B2               5-5-2011            3
B2               1-1-2011           15
B2               1-1-2011            4
B2               5-5-2011            5
B2               5-5-2011            1

The result shout be: 

Pat           LastDate           Result
A1            11-11-2011          8
B2             5-5-2011           5



Here are the result given by the groupmembers, with the result en at last the correct solution.
 

1--------------



SELECT tbl1.Pat, Last(tbl1.Date) AS LastDate, Last(tbl1.Result) AS LastResult
FROM tbl1
GROUP BY tbl1.Pat;

Pat              LastDate            LastResult
A1               11-11-2011          6
B2                5-5-2011           1



2---------------  



SELECT tbl1.Pat, Max(tbl1.Date) AS MaxDate, Max(tbl1.Result) AS MaxResult
FROM tbl1
GROUP BY tbl1.Pat;

Pat                MaxDate             MaxResult
A1                 11-11-2011          8
B2                   5-5-2011          6
 


3---------------



SELECT tbl1.Pat, Last(tbl1.Date) AS LastDate, tbl1.Result
FROM tbl1 
INNER JOIN (SELECT Max(tbl1.Date) AS MaxDate FROM tbl1)  B ON tbl1.Date=B.MaxDate
GROUP BY tbl1.Pat, tbl1.Result;

Pat               LastDate              Result
A1                 11-11-2011           6
A1                 11-11-2011           8



4-------------



SELECT Tbl1.Pat, Tbl1.Date, Tbl1.Result
FROM Tbl1
WHERE (((Tbl1.Date)=(Select Max(t2.Date) From Tbl1 as t2 Where t2.Pat = Tbl1.Pat)));


Pat          Date                Result
A1           11-11-2011          8
A1           11-11-2011          6
B2            5-5-2011           3
B2            5-5-2011           5
B2            5-5-2011           1

This solution from Wiliam gives me the all the results with the last date.


These i can easily query by:

SELECT Query5.Pat, Query5.Date, Max(Query5.Result) AS MaxVanResult
FROM Query5
GROUP BY Query5.Pat, Query5.Date;

Pat           Date               MaxVanResult
A1            11-11-2011          8
B2              5-5-2011          5


Thanks en best wishes

Pedro


p.s.  David, normally i never use "date" as a fieldname. I was just for this example.