在MS Access中为混合方案选择贷款ID [英] Selecting Loan Id for Mixed Scenarios in MS Access

问题描述

这是我的贷款表的示例:

Here is a sample of my Loan table:

Loanno  Balance amount  LoanID
1001045 308731.770000   1
1001045 2007700.740000  2
1001045 3087318905.770  3
1001045 308731.770000   4
1001046 306589.67       1
1001046 456321.23       1
1001046 6932542.89      1
1001047 582563.56       2
1001047 965421.34       2

1. 如果对于Loanno，LoanId是不同的，即(1,2,3,4,5)，那么我必须填充一个名为 LoanIndex为'6'.

1. If for a Loanno the LoanId is different, i.e (1,2,3,4,5), then I have to populate a field called LoanIndex as '6'.

否则，如果对于一个贷款编号，Loanid值都相同，即全1或全2，那么我必须将LoanIndex填充为"1"或"2".

Otherwise if for a Loannumber the Loanid values are all the same, i.e all 1's or all 2's, then I have to populate LoanIndex as either '1' or '2'.

我的最终输出应如下所示:

My final output should look like this:

Loanno   LoanIndex
1001045    6
1001046    1
1001047    2

推荐答案

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

Start with a query which gives you all unique combinations of Loanno and LoanID.

SELECT DISTINCT
    Loanno,
    LoanID
FROM [Loan Table]

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

Then use that SQL as a subquery and compute the count of unique LoanID values for each 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

最后将其与您的[Loan Table]结合起来，并使用IIf()表达式，如果CountOfLoanID> 1则返回6，否则返回LoanID.

Finally join that with your [Loan Table] and use an IIf() expression which returns 6 if CountOfLoanID is > 1, or LoanID otherwise.

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);

这篇关于在MS Access中为混合方案选择贷款ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！