By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,928 Members | 1,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,928 IT Pros & Developers. It's quick & easy.

Selecting Specific Dates

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.