Forum Discussion

Tony2021's avatar
Tony2021
Iron 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
Bronze Contributor
Nov 06, 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;

 

 

  • Tony2021's avatar
    Tony2021
    Iron Contributor
    Nov 06, 2022
    Hi harun, that is very nice. It is a little complicated for me though. I am a basic user and know only basic coding in Access. I have to change up the queries in your code to use in my db since the db I posted was only a simple example. Your code does work in the simple example I posted. I can confirm that.

    I will likely need to make further edits down the road and I would not know how to edit your code. Arnel's example is a little easier for me to follow since I see the union query and its relationship to the other ones. I think yours is a nested query and the technical level is way above me unfortunately.

    thank you very much for the help.

Resources