Results 1 to 5 of 5

Insert Into Select (with UNIONS (if possible))

  1. #1
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    59

    Insert Into Select (with UNIONS (if possible))

    Hi all,

    I am trying to run the following query and the UNIONs seem to be causing a problem, so I broke out the 3 different queries and decided to insert each individually. I though that would be no problem, but I am getting a syntax error on the SELECT statement. So....I decided to go back to the UNIONs and post the question in here to see if I could get some help.

    HTML Code:
    INSERT INTO duplicate_table 
                ( 
                            duplicate_status, 
                            number_of_duplicates, 
                            tracking_number, 
                            invoice_number, 
                            account_number, 
                            net_amount, 
                            billed_weight, 
                            invoice_date, 
                            transaction_date, 
                            pickup_record_number, 
                            receiver_name, 
                            receiver_company_name, 
                            receiver_address_line_1, 
                            receiver_address_line_2, 
                            receiver_city, 
                            receiver_state, 
                            receiver_postal, 
                            receiver_country_territory 
                ) 
                VALUES 
                ( 
                           SELECT     'duplicate in history' AS duplicate_status, 
                                      Count(*)               AS number_of_duplicates, 
                                      sd.tracking_number     AS tracking_number, 
                                      sd.invoice_number      AS invoice_number, 
                                      sd.account_number      AS account_number, 
                                      sd.net_amount          AS net_amount, 
                                      sd.billed_weight       AS billed_weight, 
                                      sd.invoice_date        AS invoice_date, 
                                      sd.transaction_date    AS transaction_date, 
                                      sd.pickup_record_number, 
                                      sd.receiver_name              AS receiver_name, 
                                      sd.receiver_company_name      AS receiver_company_name, 
                                      sd.receiver_address_line_1    AS receiver_address_line_1, 
                                      sd.receiver_address_line_2    AS receiver_address_line_2, 
                                      sd.receiver_city              AS receiver_city, 
                                      sd.receiver_state             AS receiver_state, 
                                      sd.receiver_postal            AS receiver_postal, 
                                      sd.receiver_country_territory AS receiver_country_territory 
                           FROM       workspace_1157176226          AS sd 
                           INNER JOIN duplicate_verification        AS dv 
                           ON         sd.tracking_number = dv.tracking_number 
                           AND        sd.account_number = dv.account_number 
                           WHERE      sd.charge_classification_code = 'FRT' 
                           AND        sd.bill_option_code NOT IN ( 'DTP', 
                                                                  'DFC' ) 
                           AND        ( 
                                                 sd.charge_category_code = 'SHP' 
                                      OR         sd.charge_category_detail_code = 'CLB' ) 
                           AND        sd.tracking_number <> '' 
                           AND        net_amount > 0 
                           GROUP BY   sd.tracking_number, 
                                      sd.invoice_number, 
                                      sd.account_number, 
                                      sd.net_amount, 
                                      sd.billed_weight, 
                                      sd.invoice_date, 
                                      sd.transaction_date, 
                                      sd.pickup_record_number, 
                                      sd.receiver_name, 
                                      sd.receiver_company_name, 
                                      sd.receiver_address_line_1, 
                                      sd.receiver_address_line_2, 
                                      sd.receiver_city, 
                                      sd.receiver_state, 
                                      sd.receiver_postal, 
                                      sd.receiver_country_territory 
                           UNION ALL 
                           SELECT     'original'          AS duplicate_status, 
                                      NULL                AS number_of_duplicates, 
                                      dv.tracking_number  AS tracking_number, 
                                      dv.invoice_number   AS invoice_number, 
                                      dv.account_number   AS account_number, 
                                      dv.freight_charges  AS net_amount, 
                                      dv.billed_weight    AS billed_weight, 
                                      dv.invoice_date     AS invoice_date, 
                                      dv.transaction_date AS transaction_date, 
                                      dv.pickup_record_number, 
                                      dv.receiver_name              AS receiver_name, 
                                      dv.receiver_company_name      AS company_name, 
                                      dv.receiver_address_line_1    AS receiver_address_line_1, 
                                      dv.receiver_address_line_2    AS receiver_address_line_2, 
                                      dv.receiver_city              AS receiver_city, 
                                      dv.receiver_state             AS receiver_state, 
                                      dv.receiver_postal            AS receiver_postal, 
                                      dv.receiver_country_territory AS receiver_country_territory 
                           FROM       workspace_1157176226          AS sd 
                           INNER JOIN duplicate_verification        AS dv 
                           ON         sd.tracking_number = dv.tracking_number 
                           AND        sd.account_number = dv.account_number 
                           WHERE      sd.charge_classification_code = 'FRT' 
                           AND        sd.bill_option_code NOT IN ( 'DTP', 
                                                                  'DFC' ) 
                           AND        ( 
                                                 sd.charge_category_code = 'SHP' 
                                      OR         sd.charge_category_detail_code = 'CLB' ) 
                           AND        sd.tracking_number <> '' 
                           AND        net_amount > 0 
                           GROUP BY   dv.tracking_number, 
                                      dv.invoice_number, 
                                      dv.account_number, 
                                      dv.freight_charges, 
                                      dv.billed_weight, 
                                      dv.invoice_date, 
                                      dv.transaction_date, 
                                      dv.pickup_record_number, 
                                      dv.receiver_name, 
                                      dv.receiver_company_name, 
                                      dv.receiver_address_line_1, 
                                      dv.receiver_address_line_2, 
                                      dv.receiver_city, 
                                      dv.receiver_state, 
                                      dv.receiver_postal, 
                                      dv.receiver_country_territory 
                ) 
     union 
           ( 
                  SELECT duplicate_status, 
                         number_of_duplicatesx - 1 AS number_of_duplicates, 
                         tracking_number, 
                         invoice_number, 
                         account_number, 
                         net_amount, 
                         billed_weight, 
                         invoice_date, 
                         transaction_date, 
                         pickup_record_number, 
                         receiver_name, 
                         company_name, 
                         receiver_address_line_1, 
                         receiver_address_line_2, 
                         receiver_city, 
                         receiver_state, 
                         receiver_postal, 
                         receiver_country_territory 
                  FROM   ( 
                                    SELECT     'duplicate in batch loaded' AS duplicate_status, 
                                               count(*)                    AS number_of_duplicatesx,
                                               sd.tracking_number          AS tracking_number, 
                                               sd.invoice_number           AS invoice_number, 
                                               sd.account_number           AS account_number, 
                                               sd.net_amount               AS net_amount, 
                                               sd.billed_weight            AS billed_weight, 
                                               sd.invoice_date             AS invoice_date, 
                                               sd.transaction_date         AS transaction_date, 
                                               sd.pickup_record_number, 
                                               sd.receiver_name              AS receiver_name, 
                                               sd.receiver_company_name      AS company_name, 
                                               sd.receiver_address_line_1    AS receiver_address_line_1,
                                               sd.receiver_address_line_2    AS receiver_address_line_2,
                                               sd.receiver_city              AS receiver_city, 
                                               sd.receiver_state             AS receiver_state, 
                                               sd.receiver_postal            AS receiver_postal, 
                                               sd.receiver_country_territory AS receiver_country_territory
                                    FROM       workspace_1157176226          AS sd 
                                    INNER JOIN 
                                               ( 
                                                      SELECT tracking_number, 
                                                             account_number 
                                                      FROM   workspace_1157176226 AS xxx 
                                                      WHERE  xxx.charge_classification_code = 'FRT'
                                                      AND    xxx.bill_option_code NOT IN ( 'DTP', 
                                                                                          'DFC' ) 
                                                      AND    ( 
                                                                    xxx.charge_category_code = 'SHP'
                                                             OR     xxx.charge_category_detail_code = 'CLB' )
                                                      AND    xxx.tracking_number <> '' 
                                                      AND    xxx.net_amount > 0) AS dv 
                                    ON         sd.tracking_number = dv.tracking_number 
                                    AND        sd.account_number = dv.account_number 
                                    WHERE      sd.charge_classification_code = 'FRT' 
                                    AND        sd.bill_option_code NOT IN ( 'DTP', 
                                                                           'DFC' ) 
                                    AND        ( 
                                                          sd.charge_category_code = 'SHP' 
                                               OR         sd.charge_category_detail_code = 'CLB' ) 
                                    AND        sd.tracking_number <> '' 
                                    AND        sd.net_amount > 0 
                                    GROUP BY   sd.tracking_number, 
                                               sd.invoice_number, 
                                               sd.account_number, 
                                               sd.net_amount, 
                                               sd.billed_weight, 
                                               sd.invoice_date, 
                                               sd.transaction_date, 
                                               sd.pickup_record_number, 
                                               sd.receiver_name, 
                                               sd.receiver_company_name, 
                                               sd.receiver_address_line_1, 
                                               sd.receiver_address_line_2, 
                                               sd.receiver_city, 
                                               sd.receiver_state, 
                                               sd.receiver_postal, 
                                               sd.receiver_country_territory ) 
                  WHERE  number_of_duplicatesx > 1)
    The error is:



    Syntax error in query expression 'SELECT 'duplicate in history' AS duplicate_status'.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,722
    The issue may be use of keyword VALUES with nested query. Correct syntax is:

    INSERT INTO table(field1, field2, field3) SELECT field1, field2, field3 FROM otherSource

    So build union query object and make sure that will open and show desired data. Then:

    INSERT INTO duplicate_table(…) SELECT ... FROM unionQuery

    If you want to have a single query object, try:

    INSERT INTO duplicate_table(…) SELECT ... FROM (SELECT ... UNION SELECT...)

    If fields in destination table design and source table/query are in exactly same order, try:

    INSERT INTO duplicate_table SELECT * FROM (SELECT ... UNION SELECT...)
    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.

  3. #3
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    59
    Thanks June7

    The query, without INSERT INTO table(...) works fine, but I get an error with or without the VALUES keyword.

    I'm going to review my UNIONS again to make sure they are all wraps in parenthesis.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    'duplicate in history'
    That is a table or query name? If so, it can't be quoted like that - would have to be [duplicate in history]
    If that turns out to be the issue, you just learned why object names should never have spaces or special characters, save for perhaps the underscore.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,722
    I presumed 'duplicate in history' and 'original' and 'duplicate in batch loaded' are literal text.

    I tested my suggestions and they all worked.
    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. Calculations and Unions in a query
    By Chky071 in forum Access
    Replies: 3
    Last Post: 04-24-2015, 09:06 AM
  2. Replies: 3
    Last Post: 12-13-2014, 01:47 PM
  3. How many Unions can I make in a single query?
    By accessmatt in forum Queries
    Replies: 2
    Last Post: 10-26-2014, 07:20 PM
  4. Insert Into Select Where Issues
    By VFWMain in forum Queries
    Replies: 1
    Last Post: 03-11-2014, 05:22 PM
  5. Insert value and select ID
    By FinalByte in forum Access
    Replies: 2
    Last Post: 10-31-2013, 08:53 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
  •  
Tech Forums: Microsoft Office Forums