CODEWITHSUNDEEP
sqlms-access
Andrew
Andrew

Reputation: 7788

SQL Query. Intersect two tables: show matching records; BUT, in addition, show records if they exist in either table

I have a query, but I can't figure out how to modify it to serve my needs.

SELECT POST_DATE, R1SUM AS Batch, R2SUM AS DailySF, Format( (R2SUM/(R2SUM+R1SUM)), 'Percent') AS Throughput
FROM 
(SELECT (COL1+COL2+COL3+COL4) as R1SUM, DAY
FROM DVDR)  AS r1, 
(SELECT  sum(TOTAL) as R2SUM, POST_DATE
FROM SENTSF
WHERE TMC=444)  AS r2
WHERE DAY=POST_DATE

When table R1 has no matching days in R2 (or vice versa), this query does not show the entry; but I need to reflect it with 0 values for missing record).

Must work in MSAccess.

Table1:
DATE  | COL1 | COL2 | COL3 | COL4 | 
1-1-1 |  2   |  1   |   1  |   1  |
1-1-1 |  4   |  5   |   1  |   1  |
1-2-1 |  1   |  2   |   2  |   2  |

Table2 :
DATE  | TOTAL| 
1-1-1 |  2   | 
1-1-1 |  6   |

I want it to show the final table:

DATE  | DailyBatch | DailySF | Throughput| 
1-1-1 |  16         |  8     |     50%   |
1-2-1 |  7          |  0     |      0%   |

Upvotes: 0

Views: 771

Answers (1)

user359040
user359040

Reputation:

With a UNION:

SELECT POST_DATE, 
       sum(R1SUM) AS Daily_Batch, 
       sum(R2SUM) AS Daily_SFs, 
       Format( (sum(R2SUM)/sum(R2SUM+R1SUM)), 'Percent') AS Throughput
FROM 
(SELECT DAY as POST_DATE, (
        COL1+COL2+COL3+COL4) as R1SUM, 
        0 as R2SUM 
 FROM D_VDR 
 union all
 SELECT POST_DATE, 
        0 as R1SUM, 
        sum(TOTAL) as R2SUM 
 FROM SENT_SF 
 WHERE TMC=444
) AS sq
group by POST_DATE

(Essentially, this is simulating a full outer join, which I understand is not available in Access.)

Upvotes: 3

Related Questions