Can build individual SELECT queries then copy/paste into UNION.
There is no GROUP BY clause for the aggregation.
Are you saying there could be an Actual without a corresponding Budget?
Are you using autonumber primary keys?
Advise not to use spaces in naming convention. Also advise to not use exact same field name in multiple tables.
Note the use of Null as place holder for columns.
Consider:
Code:
SELECT GL, CostCenter, Department, Period, Grouping, Sum(Actual) AS Act, Sum(Budget) AS SumBud FROM
(
SELECT Actual.GL, CostCenter, Department, Period, Grouping, Actual, Null AS Budget, "Actual" AS Category
FROM CCMapping INNER JOIN (Actual INNER JOIN GLMapping ON Actual.GL = GLMapping.GL) ON CCMapping.CC = Actual.CostCenter
UNION
SELECT Budget.GL, CostCenter, Department, Per, Grouping, Null, Budget, "Budget"
FROM CCMapping INNER JOIN (Budget INNER JOIN GLMapping ON Budget.GL = GLMapping.GL) ON CCMapping.CC = Budget.CostCenter
) AS T
GROUP BY GL, CostCenter, Department, Period, Grouping
;
or
Code:
SELECT T.GL, T.CostCenter, T.Period, CCMapping.Department, GLMapping.Grouping, Sum(T.Actual) AS Act, Sum(T.Budget) AS SumBud
FROM CCMapping INNER JOIN (
(SELECT GL, CostCenter, Period, Actual, Null AS Budget, "Actual" AS Category FROM Actual
UNION
SELECT GL, CostCenter, Per, Null, Budget, "Budget" FROM Budget) AS T
INNER JOIN GLMapping ON T.GL = GLMapping.GL) ON CCMapping.CC = T.CostCenter
GROUP BY T.GL, T.CostCenter, T.Period, CCMapping.Department, GLMapping.Grouping;