INSERT INTO Query Question

[Rachel]

Just from googling this, it appears this is a common error, but all of the
solutions suggested don't seem to solve my problem. I'm receiving the
following error message as I attempt to run an append query: The INSERT INTO
statement contains the following unknown field name: '[Company]'. Make sure
you have typed the name correctly, and try the operation again. It seems
common reasons for this error are misspellings, extra spaces, use of a
reserved word (which as far as I can determine, Company is not) or using
punctuation in the field name. I have none of these issues, yet I continue to
get the error message. The field Company is in the same position in both
tables. I'm new to access, and don't have experience with code. I'd
appreciate any help.

 

[fredg]

Just from googling this, it appears this is a common error, but all of the
solutions suggested don't seem to solve my problem. I'm receiving the
following error message as I attempt to run an append query: The INSERT INTO
statement contains the following unknown field name: '[Company]'. Make sure
you have typed the name correctly, and try the operation again. It seems
common reasons for this error are misspellings, extra spaces, use of a
reserved word (which as far as I can determine, Company is not) or using
punctuation in the field name. I have none of these issues, yet I continue to
get the error message. The field Company is in the same position in both
tables. I'm new to access, and don't have experience with code. I'd
appreciate any help.

You really should post the 'EXACT' full SQL.
Also let us know the datatype of the fields involved.

 

[Rachel]

As I mentioned, I am a beginner at this. I have no experience with SQL, and
I'm not even sure where or what to copy to show to you. If you can tell me
what you need to look at more specifically, I can post it. The fields in both
tables are text fields, with one date field and three yes/no fields. The
Company field that I am having issues with is however, text, in both tables.
--
RachelT

Just from googling this, it appears this is a common error, but all of the
solutions suggested don't seem to solve my problem. I'm receiving the
following error message as I attempt to run an append query: The INSERT INTO
statement contains the following unknown field name: '[Company]'. Make sure
you have typed the name correctly, and try the operation again. It seems
common reasons for this error are misspellings, extra spaces, use of a
reserved word (which as far as I can determine, Company is not) or using
punctuation in the field name. I have none of these issues, yet I continue to
get the error message. The field Company is in the same position in both
tables. I'm new to access, and don't have experience with code. I'd
appreciate any help.

You really should post the 'EXACT' full SQL.
Also let us know the datatype of the fields involved.

 

[fredg]

As I mentioned, I am a beginner at this. I have no experience with SQL, and
I'm not even sure where or what to copy to show to you. If you can tell me
what you need to look at more specifically, I can post it. The fields in both
tables are text fields, with one date field and three yes/no fields. The
Company field that I am having issues with is however, text, in both tables.

Open your query in Design View.
On the Menu, click on View + SQL View
Copy the entire text to the clipboard.

Open a reply message to this message.
Paste your SQL.

 

[Rachel]

INSERT INTO [total list] ( [Company], [Address], [City], [State], [Zip],
[Carrier Rt], [Zip +4], [SIC], [SIC Description], [Web Address], [Duns
Number], [DSF Score], [Rec/Company], [Prim/SEC/SIC Flag], [Title], [Full
Name], [Phone], [Full Zip], [SIC Omit], [Current Customer], [Past Customer],
[Potential Customer], [Date Added], )
SELECT [prospect list].Company, [prospect list].Address, [prospect
list].City, [prospect list].State, [prospect list].Zip, [prospect
list].[Carrier Rt], [prospect list].[Zip +4], [prospect list].SIC, [prospect
list].[SIC Description], [prospect list].[Web Address], [prospect list].[Duns
Number], [prospect list].[DSF Score], [prospect list].[Rec/Company],
[prospect list].[Prim/SEC/SIC Flag], [prospect list].Title, [prospect
list].[Full Name], [prospect list].Phone, [prospect list].[Full Zip],
[prospect list].[SIC Omit], [prospect list].[Current Customer], [prospect
list].[Past Customer], [prospect list].[Potential Customer], [prospect
list].[Date Added], [prospect list].Email
FROM [prospect list];

 

[fredg]

On Thu, 25 Sep 2008 13:36:07 -0700, Rachel wrote:

Just from googling this, it appears this is a common error, but all of
the solutions suggested don't seem to solve my problem. I'm receiving
the following error message as I attempt to run an append query: The
INSERT INTO statement contains the following unknown field name:
'[Company]'. Make sure you have typed the name correctly, and try the
operation again. It seems common reasons for this error are
misspellings, extra spaces, use of a reserved word (which as far as I
can determine, Company is not) or using punctuation in the field name.
I have none of these issues, yet I continue to get the error message.
The field Company is in the same position in both tables. I'm new to
access, and don't have experience with code. I'd appreciate any help.

INSERT INTO [total list] ( [Company], [Address], [City], [State], [Zip],
[Carrier Rt], [Zip +4], [SIC], [SIC Description], [Web Address], [Duns
Number], [DSF Score], [Rec/Company], [Prim/SEC/SIC Flag], [Title], [Full
Name], [Phone], [Full Zip], [SIC Omit], [Current Customer], [Past Customer],
[Potential Customer], [Date Added], )
SELECT [prospect list].Company, [prospect list].Address, [prospect
list].City, [prospect list].State, [prospect list].Zip, [prospect
list].[Carrier Rt], [prospect list].[Zip +4], [prospect list].SIC, [prospect
list].[SIC Description], [prospect list].[Web Address], [prospect list].[Duns
Number], [prospect list].[DSF Score], [prospect list].[Rec/Company],
[prospect list].[Prim/SEC/SIC Flag], [prospect list].Title, [prospect
list].[Full Name], [prospect list].Phone, [prospect list].[Full Zip],
[prospect list].[SIC Omit], [prospect list].[Current Customer], [prospect
list].[Past Customer], [prospect list].[Potential Customer], [prospect
list].[Date Added], [prospect list].Email
FROM [prospect list];

Regarding:
[prospect list].[Potential Customer], [prospect
list].[Date Added], [prospect list].Email
FROM [prospect list];[/QUOTE]

The only thing that jumps out at me is you are missing a space between
[prospect list].Email
and
From [prospect list];
V
[prospect list].EmailFROM [prospect list];

This will cause an error, as Access is looking for a field named
EMailFrom.

It should read...
[prospect list].Email FROM [prospect list];

If there are additional errors...
1) Try placing brackets around all field names, not just the ones with
spaces.
2) It that doesn't help try removing everything except the [company]
field.

INSERT INTO [total list] ([Company]) SELECT [prospect list].[Company]
FROM [prospect list];

Run it.
Then if that does add the company to the table, add each new field,
one at a time, to the SQL.
Run it after each added field until it fails, at which point you will
have a place to look.
See what happens, but I do think placing that space after Email will
fix it.

 

[Rachel]

Thank you.. adding the space after Email seemed to work, but when I run the
query I get the following response: "Microsoft Access can't append all the
records in the append query. Microsoft Access set 3 field(s) to Null due to a
type conversion failure and it didn't add 0 record(s) to the table due to key
violations, 0 record(s) due to lock violations, and 0 record(s) due to
validation rule violations.

When I tried adding brackets around all the field names, the changes won't
save, I get an error message that says number of query values and destination
fields are not the same, which doesn't make sense to me because they are, I
have the same number of fields in both tables.
--
RachelT

On Thu, 25 Sep 2008 13:36:07 -0700, Rachel wrote:

Just from googling this, it appears this is a common error, but all of
the solutions suggested don't seem to solve my problem. I'm receiving
the following error message as I attempt to run an append query: The
INSERT INTO statement contains the following unknown field name:
'[Company]'. Make sure you have typed the name correctly, and try the
operation again. It seems common reasons for this error are
misspellings, extra spaces, use of a reserved word (which as far as I
can determine, Company is not) or using punctuation in the field name.
I have none of these issues, yet I continue to get the error message.
The field Company is in the same position in both tables. I'm new to
access, and don't have experience with code. I'd appreciate any help.

INSERT INTO [total list] ( [Company], [Address], [City], [State], [Zip],
[Carrier Rt], [Zip +4], [SIC], [SIC Description], [Web Address], [Duns
Number], [DSF Score], [Rec/Company], [Prim/SEC/SIC Flag], [Title], [Full
Name], [Phone], [Full Zip], [SIC Omit], [Current Customer], [Past Customer],
[Potential Customer], [Date Added], )
SELECT [prospect list].Company, [prospect list].Address, [prospect
list].City, [prospect list].State, [prospect list].Zip, [prospect
list].[Carrier Rt], [prospect list].[Zip +4], [prospect list].SIC, [prospect
list].[SIC Description], [prospect list].[Web Address], [prospect list].[Duns
Number], [prospect list].[DSF Score], [prospect list].[Rec/Company],
[prospect list].[Prim/SEC/SIC Flag], [prospect list].Title, [prospect
list].[Full Name], [prospect list].Phone, [prospect list].[Full Zip],
[prospect list].[SIC Omit], [prospect list].[Current Customer], [prospect
list].[Past Customer], [prospect list].[Potential Customer], [prospect
list].[Date Added], [prospect list].Email
FROM [prospect list];

Regarding:
[prospect list].[Potential Customer], [prospect
list].[Date Added], [prospect list].Email
FROM [prospect list];[/QUOTE]

The only thing that jumps out at me is you are missing a space between
[prospect list].Email
and
From [prospect list];
V
[prospect list].EmailFROM [prospect list];

This will cause an error, as Access is looking for a field named
EMailFrom.

It should read...
[prospect list].Email FROM [prospect list];

If there are additional errors...
1) Try placing brackets around all field names, not just the ones with
spaces.
2) It that doesn't help try removing everything except the [company]
field.

INSERT INTO [total list] ([Company]) SELECT [prospect list].[Company]
FROM [prospect list];

Run it.
Then if that does add the company to the table, add each new field,
one at a time, to the SQL.
Run it after each added field until it fails, at which point you will
have a place to look.
See what happens, but I do think placing that space after Email will
fix it.[/QUOTE]