Selecting Specific Dates

[edwin.villamayor]

Hello,

I'm still trying to write sql on Access but I always seem to mess up
with the syntax. I want to write a query from a table called tb_Test:

LoanNbr Date Rating
123456 02/15/05 6
987654 05/06/04 5
987654 04/09/02 4
565864 01/07/03 7

This is what the result should look like:

LoanNbr Date Rating
123456 02/15/05 6
987654 05/06/04 5
565864 01/07/03 7

Notice that there are two records for the same LoanNbr, but I want to
choose the record with the latest date.

Any help would be very appreciated.

Thanks.

Edwin

[Peter]

Select * from tb_test inner join
(Select LoanNbr, max([date]) as Mdate from tb_test group by LoanNbr) as q1
on tb_test.loannbr = q1.loannbr and tb_test.[date] = q1.mdate

Peter
<ed**************@citigroup.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

Hello,

I'm still trying to write sql on Access but I always seem to mess up
with the syntax. I want to write a query from a table called tb_Test:

LoanNbr Date Rating
123456 02/15/05 6
987654 05/06/04 5
987654 04/09/02 4
565864 01/07/03 7

This is what the result should look like:

LoanNbr Date Rating
123456 02/15/05 6
987654 05/06/04 5
565864 01/07/03 7

Notice that there are two records for the same LoanNbr, but I want to
choose the record with the latest date.

Any help would be very appreciated.

Thanks.

Edwin