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 problem I have is that I need to show all records from both tables so I need to combine a left join and a right join but I think it can also be done by a nested query.  

I have a grouping by quarter. 

the below is the output I am looking for (i put it in excel)

 

Please see attached simple db.  There are 2 tables and 2 queries (aggregate).  

 

let me know if any questions.  thank you very much. 

 

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

4 Replies

Sort By
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    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

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Nov 06, 2022
      Hi Arnel, very nice indeed. I can follow it. I had to recreate it in my db. It works. thanks for the tip about using 'datepart' and how it converts to a number i/o using 'format' as was in the db I posted.

      thank you very much once again. Have a good one.
  • 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
      Steel 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