从查询开始，该查询为您提供Loanno和的所有唯一组合LoanID。

SELECT DISTINCT
    Loanno,
    LoanID
FROM [Loan Table]

然后使用该SQL作为子查询，并LoanID为每个查询计算唯一值的数量Loanno。

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

最后，将其与your连接，[Loan Table]并使用一个IIf()表达式，如果CountOfLoanID>> 1，则返回6
LoanID。

SELECT
    lt.Loanno,
    IIf(counts.CountOfLoanID>1, 6, lt.LoanID) AS LoanIndex
FROM
    [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.Loanno
GROUP BY
    lt.Loanno,
    IIf(counts.CountOfLoanID>1, 6, lt.LoanID);