CODEWITHSUNDEEP
sqlms-access
sharkantipav
sharkantipav

Reputation: 139

SumIF Access - Multiple tables

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

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 are linked to Broker.BRKR_CODE and can be equal.

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"

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

Upvotes: 1

Views: 1057

Answers (1)

Brad
Brad

Reputation: 12255

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

Upvotes: 2

Related Questions