Results 1 to 4 of 4
  1. #1
    waterpower is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    2

    SQL Join and Union query on the same table

    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

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,726
    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


  3. #3
    waterpower is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    2
    Quote Originally Posted by ranman256 View Post
    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?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,956
    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;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Inner Join in Union Query
    By RAJESHKUMAR R in forum Queries
    Replies: 1
    Last Post: 02-25-2018, 12:36 AM
  2. Replies: 4
    Last Post: 01-23-2014, 03:07 PM
  3. Union query to join two crosstab queries
    By racefan91 in forum Queries
    Replies: 5
    Last Post: 09-26-2013, 10:24 AM
  4. Unequal Join or Union?
    By cap.zadi in forum Queries
    Replies: 3
    Last Post: 09-23-2011, 05:57 AM
  5. Union query (or Inner/Outer join?)
    By LilMissAttack in forum Queries
    Replies: 4
    Last Post: 10-23-2010, 12:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums