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

arnel_gp's avatar
arnel_gp
Iron 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
    Iron 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.

Resources