Results 1 to 5 of 5

Thread: Append Query will not work
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2012
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Append Query will not work

    Hi all

    I wonder if someone can help me, i have three tables of data which i an trying to covert into one master table, i am using an append query , all the heading fields in the souce table and destination table are the same and set up the same, but i keep getting an error saying in column one there is a mismatch, the headings differ.

    Has someone come across this before.

    Many thanks

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,797
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Append Query will not work

    Did you check the Data Type of each field to make sure they match?
    Can you attach the SQL code of the Append Query that is not working?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Jul 2012
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Query will not work

    Hi Joe4 see copy of code below

    INSERT INTO [Main Changeover Data] ( [Line], [Changeover Start], [Changeover Complete], [Duration], [OEE Target], [Minutes to Reach Target], [OEE Time], [Shift], [Sku], [breaks], [oee_breaks], [Raw_Duration], [CHANGEOVERTARGET], [CHANGEOVERDURATION], [description], [Shop Order 1], [Shop Order 2], [SKU1], [SKU 2], [changeover_id], [DELAY], [NOTE], [OEE_NOTE] )
    SELECT [Line 11 Aeon CO data].Line, [Line 11 Aeon CO data].[Changeover Start], [Line 11 Aeon CO data].[Changeover Complete], [Line 11 Aeon CO data].Duration, [Line 11 Aeon CO data].[OEE Target], [Line 11 Aeon CO data].[Minutes to Reach Target], [Line 11 Aeon CO data].[OEE Time], [Line 11 Aeon CO data].Shift, [Line 11 Aeon CO data].Sku, [Line 11 Aeon CO data].breaks, [Line 11 Aeon CO data].oee_breaks, [Line 11 Aeon CO data].Raw_Duration, [Line 11 Aeon CO data].CHANGEOVERTARGET, [Line 11 Aeon CO data].CHANGEOVERDURATION, [Line 11 Aeon CO data].description, [Line 11 Aeon CO data].[Shop Order 1], [Line 11 Aeon CO data].[Shop Order 2], [Line 11 Aeon CO data].SKU1, [Line 11 Aeon CO data].[SKU 2], [Line 11 Aeon CO data].changeover_id, [Line 11 Aeon CO data].DELAY, [Line 11 Aeon CO data].NOTE, [Line 11 Aeon CO data].OEE_NOTE
    FROM [Line 11 Aeon CO data];

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,797
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Append Query will not work

    Did you checks the Data Types in both fields to make sure they match each other?
    For example, if the Data Types for the SKU1 field in one table is "Text", but is "Number" in the other table, it will throw an error.

    If you cannot figure it out, here is a way of zeroing in on the error.
    1. Make a copy of the table you are copying the data into ([Main Changeover Data]). Call it something like [TEMP Main Changeover Data].
    2. Now, set up your Append Query to write the data to this TEMP table.
    3. Take away one field (probably starting with the last) from your Append Query.
    4. Try running the Append Query. If you still get errors, take away another field and try again.
    5. Keep doing this until it works. Then, you know that the last field taken away is the culprit!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Jul 2012
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Query will not work

    Thanks for your help i found the problem.
    In the query design view i noticed that the line header for each line had [] around the description of the destination table but were not around the headers for the source table, i took these away and it works ok now.

    Thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •