Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Nov 05, 2022
Solved

Combine a left and right join

Experts   I have 2 tables: tblBalance and tblPayments.  I need to combine these 2 tables and show a balance ==>(tblBalance.Balance - tblPayments.PmtAmt) = the balance (per company, yr, qtr) The ...
access
  • arnel_gp's avatar
    arnel_gp
    Nov 06, 2022

    Tony2021 

    i also made queries.

    1st is the union, 1_qryCoidUnion.

    the combine this union to your 2 queries, 2_qryResult

     

    note that i made your Year and Qtr as Numeric (as it is more efficient to Join numeric field/column  than string)

    your final query is  2_qryResult

Harun24HR's avatar
Harun24HR
Silver Contributor
Nov 05, 2022

Tony2021 You can try UNION query then sum balance, payment. Give a try to the following query. See attachment.

SELECT t1.Year, t1.Qtr, t1.COID, Sum(t1.SumOfBalance) AS SumOfBalance, Sum(t1.SumOfPmtAmt) AS SumOfPmtAmt, Sum(t1.Bal) AS Balance
FROM 

(SELECT Format([BalDate],'yyyy') AS [Year], Format([BalDate],'q') AS Qtr, tblBalances.COID, Sum(tblBalances.Balance) AS SumOfBalance, 0 AS SumOfPmtAmt, tblBalances.Balance AS Bal
FROM tblBalances
GROUP BY Format([BalDate],'yyyy'), Format([BalDate],'q'), tblBalances.COID, tblBalances.Balance
       UNION ALL 
SELECT Format([PmtDate],'yyyy') AS [Year], Format([PmtDate],'q') AS Qtr, tblPayments.COID, 0 AS SumOfBalance, Sum(tblPayments.PmtAmt) AS SumOfPmtAmt, tblPayments.PmtAmt AS Bal
FROM tblPayments
GROUP BY Format([PmtDate],'yyyy'), Format([PmtDate],'q'), tblPayments.COID, tblPayments.PmtAmt
)  AS t1

GROUP BY t1.Year, t1.Qtr, t1.COID;