SQL Access 中的 SumIF [英] SumIF in SQL Access

问题描述

您好，我是 VBA/SQL 的初学者.我有 2 张表 [Broker] 和 [OPT].[Broker] 有 2 个字段 BRKR_CODE, Status 和 [OPT] 有 5 个字段 BRKR1, BRKR2, Date, COM_BRK1, COM_BRKR2

Hello I am a beginner in VBA/SQL. I have 2 tables [Broker] and [OPT]. [Broker] have 2 fields BRKR_CODE, Status and [OPT] Have 5 fields BRKR1, BRKR2, Date, COM_BRK1, COM_BRKR2

OPT.BRKR1 和 OPT.BRKR2 链接到 Broker.BRKR_CODE 并且可以相等.

OPT.BRKR1 and OPT.BRKR2 are linked to Broker.BRKR_CODE and can be equal.

我想写这个查询.... 如果 Broker.Status="Active" 然后选择 Broker.BRKR_CODE然后对于每个 Broker.BRKR_CODE(例如CB")在 OPT.BRKR1="CB" 时做 OPT.COM_BRKR1 的总和+ OPT.COM_BRKR2 当 OPT.BRKR2="CB"

I want to write this query.... If Broker.Status="Active" then Select Broker.BRKR_CODE and then for each Broker.BRKR_CODE (for example "CB") do the Sum of OPT.COM_BRKR1 when OPT.BRKR1="CB" + Sum of OPT.COM_BRKR2 when OPT.BRKR2="CB"

我写了这段代码......但它根本不起作用......正如我所说的我是一个新手......

I wrote this code.... but it doesnot work at all... as I said I am a newbie...

    SELECT IIF(Broker.Status="Active",Broker.BRKR_CODE), Sum(OPT.COM_BRKR1)+ Sum(OPT.COM_BRKR2) AS OPT_Tot

FROM Broker INNER JOIN OPT ON (Broker.BRKR_CODE = OPT.BRKR2) AND (Broker.BRKR_CODE = OPT.BRKR1)

GROUP BY Broker.BRKR_CODE;

非常感谢

推荐答案

如果我理解正确，您需要更改查询中的两件事.第一个是将条件移动到 where 子句.你似乎只想要活跃"的东西.二是将on子句中的AND改为OR.您想要匹配任一代码.

If I understand correctly, you need to change two things in your query. The first is to move the condition to the where clause. You seem to only want "Active" things. The second is to change the AND in the on clause to OR. You want matches on either code.

SELECT Broker.BRKR_CODE,
       Sum(OPT.COM_BRKR1)+ Sum(OPT.COM_BRKR2) AS OPT_Tot
FROM Broker INNER JOIN
     OPT
     ON (Broker.BRKR_CODE = OPT.BRKR2) OR (Broker.BRKR_CODE = OPT.BRKR1)
WHERE Broker.Status = "Active"
GROUP BY Broker.BRKR_CODE;

这篇关于SQL Access 中的 SumIF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！