pull value of latest date

[Guest]

My table consists of customer loans and I want to pull the credit score tied
to the latest loan for each customer. For ex., customer made 3 loans:
2/1/2005, score = 630, 5/1/2006, score = 680, 3/31/2007, score = 650. I
would like to return the score value of the latest loan which for this
customer is 650.

Plese help. Thanks!

 

[StrayBullet via AccessMonster.com]

If the loans are in a table related to a customer table (1:M) where each loan
is its own record, a query grouping by Customer ID with max(loandate) would
give you the date of the most recent loan. A second query could be used to
display the score related to that specific loan. Along the lines of:

Q1:
SELECT CustomerID, Max(LoanDate) AS MaxLoanDate
FROM tblLoan
GROUP BY tblLoan.CustomerID;

Q2:
SELECT CustomerID, Q1.MaxLoanDate, CreditScore
FROM tblLoan
INNER JOIN ON Q1 (tbl.Loan.CustomerID=Q1.CustomerID AND LoanDate=MaxLoanDate)

WHERE (((tblLoan.LoanDate)=[Q1].[MaxLoanDate]));

There are probably a number of different ways to do this, I'm sure someone
will post a more efficient way soon. Until then, the above is untested -
you'll probably have to do some adjustments to it.

 

[StrayBullet via AccessMonster.com]

EDIT OF ABOVE:

If the loans are in a table related to a customer table (1:M) where each loan
is its own record, a query grouping by Customer ID with max(loandate) would
give you the date of the most recent loan. A second query could be used to
display the score related to that specific loan. Along the lines of:

Q1:
SELECT CustomerID, Max(LoanDate) AS MaxLoanDate
FROM tblLoan
GROUP BY tblLoan.CustomerID;

Q2:
SELECT CustomerID, Q1.MaxLoanDate, CreditScore
FROM tblLoan
INNER JOIN ON Q1 (tbl.Loan.CustomerID=Q1.CustomerID)
WHERE (((tblLoan.LoanDate)=[Q1].[MaxLoanDate]));

There are probably a number of different ways to do this, Im sure someone
will post a more efficient way soon. Until then, the above is untested -
you'll probably have to do some adjustments to it. >

My table consists of customer loans and I want to pull the credit score tied
to the latest loan for each customer. For ex., customer made 3 loans:

[quoted text clipped - 3 lines]

Plese help. Thanks!