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
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#));