Combine a left and right join

[Tony2021]

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. 

[#2]

@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;

[#3 · Accepted solution]

@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]

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.

[#5]

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.