First thanks for taking the time to review this question. I'm trying to upgrade the functionality of a legacy MS Access database to implement a FULL OUTER JOIN via UNION operator, but the SELECT statements contain aggregation via SUM and + and * arithmetic operators, when I try to combine they give me a funky answer that is multiples of the desired result. Go easy on me, it's been a long time since I programmed.
Background Info 1. Unable to change the existing schema 2. Query performance doesn't matter 3. Again, I say I didn't design this schema, I've just got to make it work.
I've tried to implement the FULL OUTER JOIN as outlined in some of these articles. http://www.databasejournal.com/features/msaccess/article.php/3516561/Implementing-the-Equivalent-of-a-FULL-OUTER-JOIN-in-Microsoft-Access.htm Full Join on MS Access
Here are the awful queries from each table that I would like to FULL OUTER JOIN, presently they work independently.
SELECT [DEPT SALES].Day, (IIf(Sum([DEPT SALES].[Total Sales]) Is Null, 0, Sum([DEPT SALES].[Total Sales]))) +
(IIf(Sum([DEPT SALES].[GST]) Is Null, 0, Sum([DEPT SALES].[GST]))) +
(IIf(Sum([DEPT SALES].[PST]) Is Null, 0, Sum([DEPT SALES].[PST]))) AS [TOTAL SALES]
FROM [DEPT SALES]
WHERE [DEPT SALES].[Biz ID] = "Sk"
GROUP BY [DEPT SALES].Day;
SELECT [TILL].Day, (IIf(Sum(TILL.[2 Count]) Is Null, 0, Sum(TILL.[2 Count]) * 2)) +
(IIf(Sum(TILL.[5 Count]) Is Null, 0, Sum(TILL.[5 Count]) * 5)) +
(IIf(Sum(TILL.[10 Count]) Is Null, 0, Sum(TILL.[10 Count]) * 10)) +
(IIf(Sum(TILL.[20 Count]) Is Null, 0, Sum(TILL.[20 Count]) * 20)) +
(IIf(Sum(TILL.[50 Count]) Is Null, 0, Sum(TILL.[50 Count]) * 50)) +
(IIf(Sum(TILL.[100 Count]) Is Null, 0, Sum(TILL.[100 Count]) * 100)) +
(IIf(Sum(TILL.[Change]) Is Null, 0, Sum(TILL.[Change]))) AS [TOTAL NOTES], ( IIf(Sum(TILL.[Check 1]) Is Null, 0, Sum(TILL.[Check 1])) ) +
( IIf(Sum(TILL.[Check 2]) Is Null, 0, Sum(TILL.[Check 2])) ) +
( IIf(Sum(TILL.[Check 3]) Is Null, 0, Sum(TILL.[Check 3])) ) +
( IIf(Sum(TILL.[Check 4]) Is Null, 0, Sum(TILL.[Check 4])) ) +
( IIf(Sum(TILL.[Check 5]) Is Null, 0, Sum(TILL.[Check 5])) ) +
( IIf(Sum(TILL.[Check 6]) Is Null, 0, Sum(TILL.[Check 6])) ) +
( IIf(Sum(TILL.[Check 7]) Is Null, 0, Sum(TILL.[Check 7])) ) +
( IIf(Sum(TILL.[Check 8]) Is Null, 0, Sum(TILL.[Check 8])) ) +
( IIf(Sum(TILL.[Check 9]) Is Null, 0, Sum(TILL.[Check 9])) ) +
( IIf(Sum(TILL.[Check 9]) Is Null, 0, Sum(TILL.[Check 9])) ) AS [TOTAL CHECK], IIf(Sum(TILL.MC) Is Null, 0, Sum(TILL.MC)) AS [TOTAL MC], IIf(Sum(TILL.Visa) Is Null, 0, Sum(TILL.Visa)) AS [TOTAL VISA], IIf(Sum(TILL.[Debit Card]) Is Null, 0, Sum(TILL.[Debit Card])) AS [TOTAL DEBIT CARD], IIf(Sum(TILL.USD) Is Null, 0, Sum(TILL.USD)) AS [TOTAL USD], (IIf(Sum(TILL.[Pd Out 1]) Is Null, 0, Sum(TILL.[Pd Out 1])) + IIf(Sum(TILL.[Pd Out 2]) Is Null, 0, Sum(TILL.[Pd Out 2])) ) AS [TOTAL PD OUT], IIf(Sum(TILL.[US ex Pd Out]) Is Null, 0, Sum(TILL.[Us ex Pd Out])) AS [TOTAL US EX PD OUT]
FROM TILL
WHERE ( ([TILL].[Biz ID])="Sk")
GROUP BY [TILL].Day;
Would be grateful, for any help. To help visualize, its for a sales reporting system to report on the total money (summed from multiple tills) and also the department sales for each day. The FULL OUTER JOIN is required because the TILL & DEPT SALES data aren't always uploaded on the same day.
