Re: INSERT INTO Query Question

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


"fredg" wrote:

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], [Email] )
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];

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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

.

Relevant Pages

  • Re: Update Query only works first time
    ... your error message indicates that 35 records are locked, which is why they cannot be updated -- do you have another process which has these records tied up? ... Microsoft Access did not update 0 fielddue to a type conversion failure, 0 recorddue to key violations, 35 recorddue to lock violations, and 0 Recorddue to validation rule violations. ... This occurs after I have run the Query the first time. ... It works the first time, but then there is a very long error message with several reasons why it does not work. ...
    (microsoft.public.access.queries)
  • Re: Update query only works the first time
    ... Here is the error message I receive: ... Microsoft Access did not update 0 fielddue to a type conversion failure, ... This occurs after I have run the Query the first time and try to run it again. ... There are several bin locations with a numeric field for how many there ...
    (microsoft.public.access.queries)
  • Re: What am I doing wrong here?
    ... Microsoft Access can't append all the records in the append query. ... and it didn't add 1 recordto the table due to key violations, ... > I'm sorry as you can see the error message was very long winded and I ...
    (microsoft.public.access.queries)
  • Re: What am I doing wrong here?
    ... This error message means that your query is trying to add records that would ... create a duplicate primary key field value. ... > Microsoft Access can't append all the records in the append query. ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... Microsoft Access MVP ... When putting together that query, ... "Duane Hookom" wrote: ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)
Loading