Results 1 to 5 of 5
  1. #1
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121

    sql syntax needed to join insert statements for append query

    I have several append queries that I would like to join into one sql query. If this is possible, I need to know the proper sql syntax to use between statements. Here are three of the queries sql statements as a sample but I have four more. The reason for so many is that I have to take several passes at the data to separate dollars and hours out of the same fields including and excluding types and dates.

    INSERT INTO [FLAT FILE OUTPUT] ( ALIAS, [EV % COMPL], E, YYYMM, [BCWS T DLRS], [BCWP T DLRS], TYPE, ELEMENT_ID, RESP )


    SELECT DATA.GROUP_ID_I AS ALIAS, DATA.PCT_COMPLETE AS [EV % COMPL], [ELEMENT MAP].E AS E, [PERIOD MAP].YYYMM, Sum(DATA.BUDGET_BASELINE) AS [BCWS T DLRS], Sum(DATA.EARNED) AS [BCWP T DLRS], [ELEMENT MAP].TYPE, [ELEMENT MAP].ELEMENT_ID, DATA.GROUP_ID_H
    FROM (DATA INNER JOIN [ELEMENT MAP] ON DATA.ELEMENT_ID = [ELEMENT MAP].ELEMENT_ID) INNER JOIN [PERIOD MAP] ON DATA.PERIOD = [PERIOD MAP].PERIOD
    GROUP BY DATA.GROUP_ID_I, DATA.PCT_COMPLETE, [ELEMENT MAP].E, [PERIOD MAP].YYYMM, [ELEMENT MAP].TYPE, [ELEMENT MAP].ELEMENT_ID, DATA.GROUP_ID_H
    HAVING ((([ELEMENT MAP].TYPE)="C"));

    INSERT INTO [FLAT FILE OUTPUT] ( ALIAS, [EV % COMPL], E, YYYMM, [BCWS HRS/UTS], [BCWP HRS/UTS], TYPE, ELEMENT_ID, RESP )
    SELECT DATA.GROUP_ID_I AS ALIAS, DATA.PCT_COMPLETE AS [EV % COMPL], [ELEMENT MAP].E AS E, [PERIOD MAP].YYYMM, Sum(DATA.BUDGET_BASELINE) AS [BCWS HRS/UTS], Sum(DATA.EARNED) AS [BCWP HRS/UTS], [ELEMENT MAP].TYPE, [ELEMENT MAP].ELEMENT_ID, DATA.GROUP_ID_H
    FROM (DATA INNER JOIN [ELEMENT MAP] ON DATA.ELEMENT_ID = [ELEMENT MAP].ELEMENT_ID) INNER JOIN [PERIOD MAP] ON DATA.PERIOD = [PERIOD MAP].PERIOD
    GROUP BY DATA.GROUP_ID_I, DATA.PCT_COMPLETE, [ELEMENT MAP].E, [PERIOD MAP].YYYMM, [ELEMENT MAP].TYPE, [ELEMENT MAP].ELEMENT_ID, DATA.GROUP_ID_H
    HAVING ((([ELEMENT MAP].TYPE)="H"));

    INSERT INTO [FLAT FILE OUTPUT] ( ALIAS, [EV % COMPL], E, YYYMM, [ETC T DLRS], TYPE, ELEMENT_ID )
    SELECT DATA.GROUP_ID_I AS ALIAS, DATA.PCT_COMPLETE AS [EV % COMPL], [ELEMENT MAP].E AS E, [PERIOD MAP].YYYMM, Sum(DATA.ACTUAL) AS [ETC T DLRS], [ELEMENT MAP].TYPE, [ELEMENT MAP].ELEMENT_ID
    FROM (DATA INNER JOIN [ELEMENT MAP] ON DATA.ELEMENT_ID = [ELEMENT MAP].ELEMENT_ID) INNER JOIN [PERIOD MAP] ON DATA.PERIOD = [PERIOD MAP].PERIOD
    GROUP BY DATA.GROUP_ID_I, DATA.PCT_COMPLETE, [ELEMENT MAP].E, [PERIOD MAP].YYYMM, [ELEMENT MAP].TYPE, [ELEMENT MAP].ELEMENT_ID, [PERIOD MAP].PERIOD, DATA.GROUP_ID_H
    HAVING (((Sum(DATA.ACTUAL))<>0) AND (([ELEMENT MAP].TYPE)="C") AND (([PERIOD MAP].PERIOD)>[Greater than what Period]));

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I doubt it possible, as you don't insert into the same fields with each statement. If you did, you could probably have union together the statements, though I haven't tested.

    INSERT INTO...
    SELECT...
    UNION ALL
    SELECT...
    UNION ALL
    SELECT...

    But those would all have to have the same fields in the same order.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have several append queries that I would like to join into one sql query
    Why? You explain why there are 7 queries but not why you'd want to mesh them all. Is it because you are manually running them one at a time and don't want the hassle? I also doubt it is possible but can't think of a reason why I would want to.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just a comment - I would suggest you take a step back and fix the naming problems in your tables.

    There are spaces in names, special characters in names, and reserved words.

    Special characters include "/", "%".

    Reserved words used = "Type", "Alias" and "Data" (as a table name).
    Problem names and reserved words in Access



    Removing the spaces, special characters and reserved words will save you lots of head slapping and hair pulling.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree with previous comments.
    Create a procedure to run each append stztement/query in turn.
    Also do follow ssanfu's advice about names.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Simple Delete query syntax help needed.
    By Edward_ in forum Queries
    Replies: 5
    Last Post: 01-30-2018, 03:38 AM
  2. Replies: 20
    Last Post: 11-27-2017, 03:54 AM
  3. VBA Syntax For Append Query
    By jo15765 in forum Programming
    Replies: 12
    Last Post: 04-10-2017, 10:39 AM
  4. SQL query help...nested join needed?
    By DBNovice in forum Access
    Replies: 2
    Last Post: 07-08-2013, 07:55 PM
  5. Simepl query needed to JOIN three tables
    By mameha1977 in forum Queries
    Replies: 2
    Last Post: 10-19-2012, 06:00 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