尝试一下:

SELECT L.Loanno, L.Balanceamount, L.DueDate
     FROM dbo.Loan L 
     INNER JOIN 
         (
          SELECT Loanno, MAX(Balancemount) as MaxBalance FROM dbo.Loan
             GROUP BY LoanNo
         ) SUB ON L.Loanno = SUB.Loanno AND L.Balanceamount = SUB.MaxBalance

子查询为每个LoanNo(无论日期如何)都返回Maximum balance 回到原始表格后，您将剩下贷款编号，最大余额和到期日.

The sub query returns the Maximum balance for each LoanNo (regardless of date) When joined back to your original table you are left with the LoanNo, Maximum Balance and Date at which this is Due.

好吧，刚刚在MS Acccess中测试了以下查询，它工作正常，将Table1替换为您的实际表名:

Ok just tested the query below in MS Acccess and it works just fine, substitute Table1 with your actual table name:

SELECT T.LoanNo, T.DueDate, T.BalanceAmount
 FROM Table1 As T
  INNER JOIN (
     SELECT T.Loanno, Max([T.Balanceamount]) AS MaxBalance
     FROM Table1 as T
     GROUP BY T.Loanno) SUB ON T.LoanNo = SUB.LoanNo AND T.BalanceAmount = SUB.MaxBalance