Append Mistake #1
By Richard Rost
4 years ago
INSERT INTO Statement Unknown Field Name
After my last video on Append Queries, I got a ton of emails from people asking about various errors they get when trying to make their queries. The #1 problem people have is the error "The INSERT INTO statement contains the unknown field name: XXX. Make sure you have typed the name correctly, and try the operation again." In this video, I'll show you what causes that and how to resolve it.
Pre-Requisite
Recommended Courses
Links
Up Next...
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, The INSERT INTO statement contains the unknown field name, Why does the append query keep telling me there's an unknown field name, Statement contains the following unknown field name error, How to fix Error 3127
Intro In this video, we will talk about the most common mistake people make when using Append Queries in Microsoft Access: getting the "INSERT INTO statement contains the unknown field name" error. I will show you how this happens, usually due to mismatched field names between your source data (like an Excel import) and your destination table, and walk you through how to fix it to ensure your data appends correctly.Transcript Welcome to another Fast Tips video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.
In today's video, we're going to address Append Query mistake number one. You get an error message saying "the INSERT INTO statement contains the unknown field name" and then whatever your field name is. Make sure you have typed the name correctly and try the operation again. How do you fix that?
I got a bunch of emails and comments from people after last week's Append Query video asking about a couple of different error messages that they get. I've seen this time after time when teaching my classes. People always have the same couple of problems that they encounter when using Append Query. So in this and the next couple of videos, I'm going to explain how to resolve the common problems that you get.
If you have not yet watched this Append Query video, go watch it now and then come back and we'll continue on with fixing some mistakes.
The first mistake most people get is this one, the INSERT INTO statement, which "INSERT INTO" is the SQL language name for an Append Query. Access calls it an Append Query; in SQL, the language in SQL Server, it's called INSERT INTO statements. It's the same thing. But it's saying it contains an unknown field name. That means you're trying to insert into a field called something that's not in the destination table.
This usually comes up when you're using the asterisk, which is why I said in the previous video, if you're a beginner, don't use the asterisk.
I want someone just beamed in. So let me set this up and show you how to fix it.
Take three.
This is my TechHelp free template. You can grab a copy off my website. I'm going to say, it's take three because the last couple takes, I said you can grab a coffee. I said, coffee on the brain. Someone send me a Starbucks, please.
You can grab a copy of this database off my website if you want to. You'll find a link down below. It's the same one we used in the last database. And I got my little Append spreadsheet down here. It's in Microsoft Excel. There it is.
We're going to import this stuff into the database and append them to the customer table. Just like we did in the last video, but I want to run through the import again to show you what most people do when they do the import.
All right, so real fast. We're going to do External Data, New Source, From File, Excel. Go grab it up on desktop. There it is. Open. Import.
First row contains column headings. Yes. Next. This is all fine. Next. No primary key. Don't need one. Next. Sheet1. And we're done.
There's my data. Looks good.
Now here's what people do when they try to do the Append query. Query Design. Bring in Sheet1. Close that. Now, at this point, change it to an Append query. CustomerT. And here's the mistake people make. They bring in the star. They want to append all the fields. They want to take all of these fields and append them into the fields in the customer table. Good enough. Run the query. There's your error message.
INSERT INTO statement contains the following field name: First name. I don't understand. I've got a First name field here. I've got a First name field here. I got First name. I got Last name. I got Address. What's the problem? Why isn't this working?
Well, the problem is when you imported this data, you kept the field names from Excel. First space Name. The space in there. Last space Name. Email space Address, which is totally different from Email. These have to match exactly. And that's why you're getting that error: INSERT INTO statement.
Access is not a mind reader. It can't tell that First space Name is the same as First name.
There are a couple of ways to fix it. You could fix it on the import if you want to. Let me show you. Delete this guy. Delete Sheet1. Yes. If you go to the import - External Data - Excel. Browse to my file. Right there. Open.
Now here's where you can fix it. First row contains column headings. Next. Here on this screen, you can change these guys so they match. First, no space, name. Last, no space, name. And then Email address is just Email. Next. No primary key. Because you don't want that ID either.
Then you have to match this thing up, which is a pain. This is a topic for another video we'll be talking about soon. No primary key. Next. Point one is fine and then finish.
Now these match these exactly. Each one of these has a corresponding field down here. Now I can create my query. Bring in Sheet1. Make it an Append query. Into CustomerT. Bring down the star. Now when I run it, nothing appears to happen. Check your table. There you go. All your records are down here.
Again, like I mentioned in the last one, there's Will Riker here, Bill Riker there. If you index this guy, no duplicates, you'll get a warning message saying you have one record that they can't import because of a duplicate key violation.
The other way you can fix this, if you're not doing an import, if you're just taking it from table to table, is literally just go in your source table, design view, and change the field names in here.
If that's not an option, if you can't match up the field names from the source with the field names with the destination table, then you can't use the star. You just can't. You can only use the star if all of the fields match up. If not, you have to bring over the fields one at a time, like I did in the last video, and match them up that way in the columns.
So there you go. There's Append Query mistake number one. I have more coming. There's at least one more mistake that I see all the time. We'll do that in the next video.
Hope you learned something. We'll see you next time.
If you want to learn more about action queries in general, I've got a five-class series that covers everything you want to know about them. It's over eight hours of lessons just on queries, mostly update queries, append queries, delete queries, make table queries, tons of different examples.
I even go over some things that aren't action queries like union queries, putting two tables together, cross tab queries, basically pivot tables in Excel. So my Microsoft Expert 13 through 17 classes, I'll put a link below that you can click on to find out more information.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, all of them, a free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you've finished the Expert classes. These are the full-length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What does the error message "the INSERT INTO statement contains the unknown field name" usually indicate in an Append Query in Access?
A. A field in the source table does not exist in the destination table
B. The data types in the tables do not match
C. The database is corrupted
D. The number of records to append is too large
Q2. In Access, what SQL statement corresponds to an Append Query?
A. UPDATE
B. INSERT INTO
C. SELECT *
D. DELETE FROM
Q3. What is a common cause of the "unknown field name" error when importing data from Excel?
A. Using numeric field names
B. Field names in the Excel file do not match exactly with the destination table
C. Importing too many records at once
D. Using formulas in Excel cells
Q4. Why should beginners avoid using the asterisk (*) in Append Queries?
A. It slows down the query
B. It only appends the first field
C. It requires all fields to have matching names in both tables
D. It deletes records automatically
Q5. If the field names in an imported table do not match those in the destination table, which method could fix the problem during import?
A. Set field names to default values
B. Change field names to match the destination table during the import steps
C. Add a primary key to each field
D. Use the 'ignore fields' option
Q6. What should you do if you cannot make the field names in your source table match those in your destination table?
A. Use the asterisk to bring all fields over
B. Append records anyway and ignore the error
C. Add fields manually one at a time in the query, matching source fields to destination fields
D. Restart Access
Q7. What warning might you get if you set a primary key on a field with "no duplicates" and run an Append Query?
A. All records appended successfully
B. Duplicate key violation, some records not imported
C. Fields not found error
D. Query timeout
Q8. What is described as an alternative solution if you are not importing data but working between tables in Access?
A. Redesign the source table using design view to change field names
B. Use only numeric fields
C. Run the query with errors enabled
D. Ignore the Append Query and use Copy/Paste
Q9. Which of the following is NOT one of the action queries mentioned in the video?
A. Append Query
B. Update Query
C. Delete Query
D. Make Union Query
Q10. What benefit do Gold members receive according to the video?
A. Download all sample databases and access to code vault
B. A free laptop
C. Free one-hour consultation per week
D. Direct access to SQL Server support
Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-A; 9-D; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers one of the most common mistakes people encounter when working with Append Queries in Microsoft Access. Many users receive an error message saying: "The INSERT INTO statement contains the unknown field name," followed by the field in question. This issue usually arises when the field names in the query do not exactly match those in the destination table.
After last week's video on Append Queries, I received several questions about different error messages. This happens frequently in my classes, so I decided to dedicate a few videos to the most frequent issues. If you have not watched the previous Append Query video, I recommend checking it out first before proceeding with these troubleshooting steps.
Let's start with the most common mistake. Access uses the term "Append Query," but in SQL, it is actually called an "INSERT INTO" statement. The error about an unknown field name usually means you are trying to append data into a field that does not match any of the fields in your destination table. This often happens when you use the asterisk (star) to append all fields at once, rather than specifying individual fields.
Let me walk you through the scenario. Imagine you are importing data from Excel into Access. Suppose you have a spreadsheet with column headings like "First name," "Last name," and "Email Address," which might look fine at first glance. After importing the data into a temporary table, you attempt to use an Append Query to move that data into your customer table, using the asterisk to select all fields.
When you run the query, you get an error stating something like "INSERT INTO statement contains the following field name: First name." Even though your tables seem to have similar fields, Access cannot match them because of subtle differences such as spaces or spelling variations. For example, "First name" (with a space) in Excel will not match "FirstName" (without a space) in your Access table. They must be an exact match. Access cannot guess your intentions.
There are a couple of ways to solve this. The first solution is to fix the field names during the import process. When you import your Excel spreadsheet into Access, you will see a screen showing the field names. At this stage, you can edit the names directly to match those in your destination table – for example, remove spaces from "First name" to create "FirstName," or change "Email Address" simply to "Email." If you do this during the import, your fields will match, and the Append Query will work flawlessly.
After making sure the field names are consistent, you use the Append Query as before. This time, when you run it, the records will be appended successfully to the customer table. If you have fields that are indexed to prevent duplicates, Access will warn you about any records that cannot be imported, such as those with duplicate primary keys.
Another way to fix this issue, if you are not importing from Excel but rather moving data between tables within Access, is to go directly into Design View for your source table and adjust the field names there to make sure they match your destination table.
If you cannot change the field names or ensure they are consistent across the tables, you must stop using the asterisk in your Append Query. Instead, specify each field individually in the query and make sure every field from your source is matched with its exact counterpart in the destination table.
This covers the number one mistake I see people make when working with Append Queries. There are several more common pitfalls which I will discuss in upcoming videos.
If you are interested in learning more about action queries, I offer a five-class series that covers all aspects of queries in Access, including update, append, delete, and make-table queries, among others. These classes also go into topics like union queries and crosstab queries, which are similar to pivot tables in Excel.
There are several membership options available for those who want to learn more. Silver members and above gain access to all extended cut TechHelp videos, free beginner classes every month, and more. Gold members can download all the sample databases I use in my videos and access my code vault, which contains many useful functions. They also receive priority when submitting TechHelp questions and a free Expert class each month after completing the beginner courses. Platinum members enjoy all these perks plus even higher priority for questions and access to all full beginner courses for every subject I teach, including Word, Excel, and Visual Basic, as well as a free Developer class each month after the Expert series.
Of course, these free TechHelp videos will always be available. As long as you keep watching, I will keep making more.
You can find a complete video tutorial with step-by-step instructions covering everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Explaining the "unknown field name" error in Append Queries
Demonstrating the cause of INSERT INTO errors due to field name mismatches
Importing data from Excel to Access and checking field names
Identifying issues with spaces and mismatched field names
Correcting field names during the Excel import process
Renaming fields in Access during import to match target table
Running an Append Query after correcting field names
Dealing with duplicate key violations after appending
Manually updating field names in table design view
Limitations when using the asterisk (*) in Append Queries
Appending specific fields when field names do not match |
