INSERT INTO error: "...contains the following unknown field name..

Discussion in 'Microsoft Access Queries' started by Brad Granath, Feb 15, 2008.

  1. Brad Granath

    Brad Granath Guest

    I'm having trouble with a multiple INSERT statement:

    INSERT INTO [Course Completion] (
    [Course Completion].EID,
    [Course Completion].Course,
    [Course Completion].[Date Completed],
    [Course Completion].[Retrain Date]
    )
    SELECT
    [Set Imported EID].EID,
    [Set Imported EID].[Course],
    [Set Imported EID].[Date Completed],
    [Set Imported EID].[Retrain Date]
    FROM [Set Imported EID];

    When I try to run it, it returns the following error:
    "The INSERT INTO statement contains the following unknown field name:
    'Course Completion.EID'. Make sure you have typed the name correctly and try
    the operation again."

    "Set Imported EID" is a query, that runs correctly, and "Course Completion"
    is a table that definitely contains the column, "EID." I don't get it. This
    is my first time using a multiple INSERT statement, so maybe I'm messing up
    the syntax somewhere, but if I am, I don't see how. It does the same thing
    if I use a single INSERT, with explicit values. I've checked the spelling
    half a dozen times, and had a co-worker go over it as well.

    Any help here would be hot.

    Thanks,
    Brad
     
    Brad Granath, Feb 15, 2008
    #1
    1. Advertisements

  2. Hi -

    Did you try creating an append query to see if that works? You could then
    paste the resulting SQL into your code.

    John


    Brad Granath wrote:
    >I'm having trouble with a multiple INSERT statement:
    >
    >INSERT INTO [Course Completion] (
    >[Course Completion].EID,
    >[Course Completion].Course,
    >[Course Completion].[Date Completed],
    >[Course Completion].[Retrain Date]
    >)
    >SELECT
    >[Set Imported EID].EID,
    >[Set Imported EID].[Course],
    >[Set Imported EID].[Date Completed],
    >[Set Imported EID].[Retrain Date]
    >FROM [Set Imported EID];
    >
    >When I try to run it, it returns the following error:
    >"The INSERT INTO statement contains the following unknown field name:
    >'Course Completion.EID'. Make sure you have typed the name correctly and try
    >the operation again."
    >
    >"Set Imported EID" is a query, that runs correctly, and "Course Completion"
    >is a table that definitely contains the column, "EID." I don't get it. This
    >is my first time using a multiple INSERT statement, so maybe I'm messing up
    >the syntax somewhere, but if I am, I don't see how. It does the same thing
    >if I use a single INSERT, with explicit values. I've checked the spelling
    >half a dozen times, and had a co-worker go over it as well.
    >
    >Any help here would be hot.
    >
    >Thanks,
    >Brad


    --
    John Goddard
    Ottawa, ON Canada
    jrgoddard at cyberus dot ca

    Message posted via http://www.accessmonster.com
     
    J_Goddard via AccessMonster.com, Feb 15, 2008
    #2
    1. Advertisements

  3. On Fri, 15 Feb 2008 09:50:01 -0800, Brad Granath
    <> wrote:

    >I'm having trouble with a multiple INSERT statement:
    >
    >INSERT INTO [Course Completion] (
    >[Course Completion].EID,
    >[Course Completion].Course,
    >[Course Completion].[Date Completed],
    >[Course Completion].[Retrain Date]
    >)
    >SELECT
    >[Set Imported EID].EID,
    >[Set Imported EID].[Course],
    >[Set Imported EID].[Date Completed],
    >[Set Imported EID].[Retrain Date]
    >FROM [Set Imported EID];
    >
    >When I try to run it, it returns the following error:
    >"The INSERT INTO statement contains the following unknown field name:
    >'Course Completion.EID'. Make sure you have typed the name correctly and try
    >the operation again."


    Try just leaving off the tablename in the insertion fields:

    INSERT INTO [Course Completion] (EID,Course,[Date Completed],[Retrain Date])
    SELECT ...

    The table name certainly isn't *needed* - you can only insert into fields in
    the target table anyway.
    --
    John W. Vinson [MVP]
     
    John W. Vinson, Feb 15, 2008
    #3
  4. Brad Granath

    Brad Granath Guest

    Re: INSERT INTO error: "...contains the following unknown field na

    Thanks.

    I guess it didn't like the fact that I was specifying the columns like:

    INSERT INTO tablename (tablename.columnname, tab...

    Instead it wanted:

    INSERT INTO tablename (columnname, col...
     
    Brad Granath, Feb 15, 2008
    #4
  5. Brad Granath

    Brad Granath Guest

    Re: INSERT INTO error: "...contains the following unknown field na

    Yep, that did it. Thanks for the help, guys.
     
    Brad Granath, Feb 15, 2008
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page