Results 1 to 5 of 5
  1. #1
    Falcon37 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    2

    The number of columns in two selected table or queries of a union query do not match


    Hello everyone i am new to Microsoft Access. I took on a process for another person. About 6 mths ago I ran the process no issues, now i running this query again and this message pops up: "The number of columns in two selected table or queries of a union query do not match" I made no adjustments or changes to the file since last run. Below is the design view of the query.

    SELECT [Union SELECT ID].ID, [Union SELECT ID].Co, [Union SELECT ID].RHH_CUST_NUMBER, [Union SELECT ID].IHH_ORDER_NUMBER, [Union SELECT ID].RHH_INV_NUMBER, [Union SELECT ID].RHH_RETURN_NUMBER, [Union SELECT ID].RHH_NET_AMT, [Union SELECT ID].IHH_NET_AMT, [Union SELECT ID].RHH_SHIPPING_AMT, [Union SELECT ID].IHH_SHIPPING_AMT, [Union SELECT ID].RHH_TAX_AMT, [Union SELECT ID].IHH_TAX_AMT, [Union SELECT ID].R_OTHER_AMT, [Union SELECT ID].RHH_MISC_AMT, [Union SELECT ID].IHH_MISC_AMT, [Union SELECT ID].RHH_TOT, [Union SELECT ID].IHH_TOTAL_AMT, [Union SELECT ID].compare AS type, [Union SELECT ID].IHH_TAX_STATE, [Union SELECT ID].Recovery
    FROM ([Union SELECT ID] LEFT JOIN [Canadian provinces] ON [Union SELECT ID].IHH_TAX_STATE=[Canadian provinces].[Province code]) LEFT JOIN [Tax adjustments Credit Code 207] ON ([Union SELECT ID].Co=[Tax adjustments Credit Code 207].Company_Num) AND ([Union SELECT ID].RHH_RETURN_NUMBER=[Tax adjustments Credit Code 207].RHH_RETURN_NUMBER) AND ([Union SELECT ID].RHH_INV_NUMBER=[Tax adjustments Credit Code 207].RHH_INV_NUMBER)
    WHERE ((([Tax adjustments Credit Code 207].Company_Num) Is Null) AND (([Tax adjustments Credit Code 207].RHH_RETURN_NUMBER) Is Null) AND (([Tax adjustments Credit Code 207].RHH_INV_NUMBER) Is Null) AND (([Canadian provinces].[Province code]) Is Null))
    ORDER BY [Union SELECT ID].ID;


    Any help would be greatly appreciated .... you can reach out to me in this thread or my email mcmichaeljl@gmail.com.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Judging by your naming convention, the error is likely to be in [Union SELECT ID] ?

    Also it helps if you write the SQL in some way that it can be read a lot easier?

    Code:
    SELECT     [Union SELECT ID].ID,
        [Union SELECT ID].Co,
        [Union SELECT ID].RHH_CUST_NUMBER,
        [Union SELECT ID].IHH_ORDER_NUMBER,
        [Union SELECT ID].RHH_INV_NUMBER,
        [Union SELECT ID].RHH_RETURN_NUMBER,
        [Union SELECT ID].RHH_NET_AMT,
        [Union SELECT ID].IHH_NET_AMT,
        [Union SELECT ID].RHH_SHIPPING_AMT,
        [Union SELECT ID].IHH_SHIPPING_AMT,
        [Union SELECT ID].RHH_TAX_AMT,
        [Union SELECT ID].IHH_TAX_AMT,
        [Union SELECT ID].R_OTHER_AMT,
        [Union SELECT ID].RHH_MISC_AMT,
        [Union SELECT ID].IHH_MISC_AMT,
        [Union SELECT ID].RHH_TOT,
        [Union SELECT ID].IHH_TOTAL_AMT,
        [Union SELECT ID].compare AS type,
        [Union SELECT ID].IHH_TAX_STATE,
        [Union SELECT ID].Recovery
    FROM ([Union SELECT ID] 
    LEFT JOIN [Canadian provinces] 
        ON [Union SELECT ID].IHH_TAX_STATE=[Canadian provinces].[Province code])
    LEFT JOIN [Tax adjustments Credit Code 207]
        ON ([Union SELECT ID].Co=[Tax adjustments Credit Code 207].Company_Num)
        AND ([Union SELECT ID].RHH_RETURN_NUMBER=[Tax adjustments Credit Code 207].RHH_RETURN_NUMBER) 
        AND ([Union SELECT ID].RHH_INV_NUMBER=[Tax adjustments Credit Code 207].RHH_INV_NUMBER)
            WHERE ((([Tax adjustments Credit Code 207].Company_Num) Is Null)
            AND (([Tax adjustments Credit Code 207].RHH_RETURN_NUMBER) Is Null)
            AND (([Tax adjustments Credit Code 207].RHH_INV_NUMBER) Is Null)
            AND (([Canadian Provinces].[Province code]) Is Null))
    ORDER BY [Union SELECT ID].ID;
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The error is generated by "[Union SELECT ID]" query, not the one you posted. Can you look at the SQL statement for that query, examine the tables\fields used and you should be able to find the discrepancy.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I am with Vlad.

    Seems the error comes from the definition of the [Union SELECT ID] query (BTW, too bad naming), probably because of the format of an amount that includes a comma (,) as thousands or decimal separator.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, be aware that you should never use spaces in any object name. Life will be easier..........

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

Similar Threads

  1. Replies: 1
    Last Post: 08-06-2020, 11:50 AM
  2. Replies: 11
    Last Post: 12-26-2018, 06:07 PM
  3. Perform a Union All Query For Two Columns in the Same Table
    By DigitalAdrenaline in forum Queries
    Replies: 14
    Last Post: 08-19-2018, 05:39 PM
  4. Replies: 5
    Last Post: 10-12-2017, 12:12 PM
  5. Replies: 2
    Last Post: 04-30-2014, 03:01 PM

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