Search code examples
ms-accessleft-joinfull-outer-join

Full outer join with multiple tables access sql left join


I have two tables with panel data (called tbl1 and tbl3) and another table that I'm using to join them together (tbl2). tbl1 has data for all participants, but tbl3 only has a subset of them. Sample data tables are:

tbl1:

partkey date energy
1 1/1/2021 567
1 2/1/2021 585
2 1/1/2021 1305
2 2/1/2021 1324
3 1/1/2021 860
3 2/1/2021 869

tbl2:

partkey assignkey
1 54
2 63
3 65

tbl3:

assignkey dt lossfactor
54 1/1/2021 .86
54 2/1/2021 .85
65 1/1/2021 .67
65 2/1/2021 .69

I'd like to combine the tables so that I get all records, not just the records common to both tables. I also have in the code to replace null values with 1. This is the desired output:

partkey date energy lossfctr
1 2/1/2021 585 .85
2 2/1/2021 1324 1
3 2/1/2021 869 .69

My code right now knows to match the id but not the date, so I get one value for a date-id combination for one of the variables (energy), but values for all dates for the other variable (lossfactor).

SELECT tbl1.partkey, tbl1.date, tbl1.energy, IIf(IsNull(tbl3.lossfactor),1,tbl3.lossfactor) AS lossfctr FROM (tbl1 INNER JOIN tbl2 ON tbl2.partkey = tbl1.partkey ) left join tbl3 on tbl2.assignkey = tbl3.assignkey WHERE ( ((tbl1.partkey=1) OR (tbl1.partkey=2) OR (tbl1.partkey=3)) AND (tbl1.date=#2/1/2021#) AND (tbl3.dt=#2/1/2021#))

UNION

SELECT tbl1.partkey, tbl1.date, tbl1.energy, IIf(IsNull(tbl3.lossfactor),1,tbl3.lossfactor) AS lossfctr FROM (tbl1 INNER JOIN tbl2 ON tbl2.partkey = tbl1.partkey ) left join tbl3 on tbl1.date = tbl3.dt WHERE ( ((tbl1.partkey=1) OR (tbl1.partkey=2) OR (tbl1.partkey=3)) AND (tbl1.date=#2/1/2021#) AND (tbl3.dt=#2/1/2021#)); `

I also tried doing the left join on both columns, id and the date, but it gives me an error.

Thank you! These two links were helpful: https://support.microsoft.com/en-us/office/join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6

https://support.microsoft.com/en-us/office/left-join-right-join-operations-ebb18b36-7976-4c6e-9ea1-c701e9f7f5fb


Solution

  • Consider:

    SELECT Query1.partkey, Query1.date, Query1.energy, Nz([lossfactor],1) AS LF
    FROM
        (SELECT tbl1.partkey, tbl1.Date, tbl1.energy, tbl2.assignkey
         FROM tbl1 INNER JOIN tbl2 ON tbl1.partkey = tbl2.partkey) AS  Query1 
    LEFT JOIN tbl3 ON (Query1.date = tbl3.dt) AND (Query1.assignkey = tbl3.assignkey)
    WHERE (((Query1.date)=#2/1/2021#));