SQL Join and Union query on the same table

[waterpower]

Hi everyone,

I have 4 tables and would like to join them.
Tables: Table: [CC Mapping], [GL Mapping], [ACTUAL], [BUDGET]
Columns:
CC Mapping: CC, Department
GL Mapping: GL, Grouping
Actual: Actual, GL, Cost Center, Period
Budget: Budget, GL, Cost Center, PER

The purpose of the query is to have the "Actual" table as the main table to contain all the data from the other 3 tables.
1. I want to do a VLookup of the Department column from the "CC Mapping table" to the "Actual"

FROM ACTUAL LEFT JOIN
ON ACTUAL.[Cost Center] = [CC Mapping].CC)

2. I want to do a Vlookup of the Grouping column from the "GL Mapping" table to the "Actual"

FROM ACTUAL LEFT JOIN
ON ACTUAL.[GL] = [GL Mapping].GL

3. I want to add the lines to the "Actual" table from the "Budget" table. What I want to carry through is the Account, Cost Center, Period and Budget columns. The Budget column is something to add on the "Actual" table as it is inexistent. I made sure that the Select statement follows the same order: Period, GL, CC, and the new column to add Budget.

(SELECT ACTUAL.[Period], ACTUAL.[GL], ACTUAL.[Cost Center]
UNION
SELECT BUDGET.[PER], BUDGET.[GL], BUDGET.[Cost Center], BUDGET.[Budget])


Combining all these 3 SQL statements on Access, I came up with the following, unfortunately it did not work out.
Access pops out an error message with the FROM syntax error:

SELECT [CC Mapping].Department, [GL Mapping].Grouping, Sum(ACTUAL.Actual) AS AC, Sum(BUDGET.Budget) AS BU
FROM ACTUAL LEFT JOIN
(FROM ACTUAL LEFT JOIN
(SELECT ACTUAL.[Period], ACTUAL.[GL], ACTUAL.[Cost Center], ACTUAL.[BUDGET]
UNION
SELECT BUDGET.[PER], BUDGET.[GL], BUDGET.[Cost Center], BUDGET.[Budget])
ON ACTUAL.[Cost Center] = [CC Mapping].CC)


ON ACTUAL.[GL] = [GL Mapping].GL

The desire outcome is having the grouping, department and budget columns added with the proper data matched like performing a VLookup.
I need to unionise Actual and Budget tables since they do not always contain the same GL, CC and having a join could not bring 100% of required data.

Thank you for your help in advance.

Mike

[ranman256]

its SO much easier (and readable) to make your individual queries then make a Union query with them:

select * from Q1
union

select * from Q2
union

select * from Q3

[waterpower]

its SO much easier (and readable) to make your individual queries then make a Union query with them:

select * from Q1
union

select * from Q2
union

select * from Q3

Hi, you mean doing 3 separate queries and a 4th one to unionsise the 3 queries?

[June7]

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:

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

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;