SumIF访问-多个表 [英] SumIF Access - Multiple tables

问题描述

我正在关注我在这里提出的问题: SQL Access中的SumIF

I am following the question I asked here: SumIF in SQL Access

我有3张桌子[经纪人]，[OPT]，[TRS]. [经纪人]有2个字段BRKR_CODE, Status，[OPT]和[TRS]有5个相同的字段BRKR1, BRKR2, Date, COM_BRK1, COM_BRKR2

I have 3 tables [Broker] , [OPT], [TRS]. [Broker] have 2 fields BRKR_CODE, Status and [OPT] and [TRS] Have 5 same fields BRKR1, BRKR2, Date, COM_BRK1, COM_BRKR2

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

OPT.BRKR1, OPT.BRKR2, TRS.BRKR1, TRS.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.BRKR2="CB"时执行OPT.COM_BRKR2的总和，而在TRS.BRKR1="CB"中执行TRS.COM_BRKR1的总和. TRS.COM_BRKR2当TRS.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" and do the Sum of TRS.COM_BRKR1 when TRS.BRKR1="CB" + Sum of TRS.COM_BRKR2 when TRS.BRKR2="CB"

我使用在上一个问题中得到的答案编写了这段代码...但是仍然存在语法问题...基本上我想要一个具有3列BRKR_CODE，OPT_Tot，TRS_Tot的数组

I wrote this code using the answer I got in the previous question... but still have syntax issue... basically I want to have an array with 3 column BRKR_CODE, OPT_Tot, TRS_Tot

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

推荐答案

您应该尝试在设计器中进行此查询(只是为了使基本连接正确).您会看到Access在括号中做了一些其他的事情，而其他SQL引擎则没有.

You should try to make this query in the designer (just to get the base joins right). You'll see Access does some different stuff with parentheses that other SQL engines don't do.

您需要将每组连接括在括号中

You need to wrap each set of joins in parentheses

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

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