在MS Access中为混合方案选择贷款ID

从查询开始，该查询为您提供

Loanno

LoanID

SELECt DISTINCT    Loanno,    LoanIDFROM [Loan Table]

然后使用该SQL作为子查询，并

LoanID

Loanno

SELECt    distinct_rows.Loanno,    Count(distinct_rows.LoanID) AS CountOfLoanIDFROM    (        SELECt DISTINCT Loanno, LoanID        FROM [Loan Table]    ) AS distinct_rowsGROUP BY distinct_rows.Loanno

最后，将其与your连接，

[Loan Table]

IIf()

CountOfLoanID

LoanID

SELECt    lt.Loanno,    IIf(counts.CountOfLoanID>1, 6, lt.LoanID) AS LoanIndexFROM    [Loan Table] AS lt    INNER JOIN        ( SELECt     distinct_rows.Loanno,     Count(distinct_rows.LoanID) AS CountOfLoanID FROM     (         SELECt DISTINCT  Loanno,  LoanID         FROM [Loan Table]     ) AS distinct_rows GROUP BY distinct_rows.Loanno        ) AS counts    ON lt.Loanno = counts.LoannoGROUP BY    lt.Loanno,    IIf(counts.CountOfLoanID>1, 6, lt.LoanID);