**Make sure that the field with your AutoNumber value does NOT appear in
your Append query. That field will take care of itself automatically (hence
the name).

I do have required entry fields in Accounts, but was not using those
fields in the query.

If you have fields that are set to "Required = Yes" in table design then one
of 2 things has to happen for an append to work:
1) "Default Value" must be set in Table design or
2) Add the field, and a value, to your append query

If there are any other fields with validation rules set in table design
(Nulls Allowed = No, Indexes set to No Duplicates, as well as ValidationRule
iteself, etc.) then those may need to be similarly addressed by the append
query as well. (AutoNumber fields being an exception).

Type conversion failure (which you aren't getting at the moment) would be
trying to put text into a numerical field, etc., (but not vice versa). Or
trying to put 1,000,000 into a field defined as Integer (max value of
32,767).

field size 50 in Access, 255 from the Excel imported table

I'm not sure this would cause the error you are seeing, but it is easy to
eliminate the possibility so you can focus on other things. In your append
query, define the value you are appending to [AccessAccountName] as
=Left([ExcelAcccountName],50) (changing the field names as necessary)

If you ever get to the point that you are trying to append 11 records and
get a "can't append 5 records" message, you can use a *copy* of your data to
run the query, determine which records Access liked/didn't like and then try
and figure out why.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"NNester" wrote in message
...
Hi George,
Thanks for the suggestions. Unfortunately, after running the Find
Unmatched
query, then attempting to append to my Accounts table, I still get the
same
message:
"... Database can't append all the records in the append query. ... set 0
field(s) to Null due to a type conversion failure, ... , and 11 record(s)
due
to validation rule violations."

I tried just adding one field "ContactName" to the QBE grid and still got
the same error. Can you help me understand the error message --
validation
rule violation? Both fields are text, field size 50 in Access, 255 from
the
Excel imported table. Do the field sizes make a difference? If so, so I
need to make sure all the field properties are identical in both tables
(hopefully not, since I would have to do this with every import). I do
have
required entry fields in Accounts, but was not using those fields in the
query.

Any other ideas? And thank you so much for your time.

Nancy

"George Nicholson" wrote:

A rough outline of one possible approach:
-Import Excel data into a new table (or, this could be an existing empty
shell that you import to every month and clear out before importing
again)
-Create a "Find Unmatched" query to identify any account names in the new
table that don't exist in your accounts table (a one-time step. You don't
run this by itself, per se, it is used by the next step.)
-Append "Unmatched" records to Accounts.

-Append "Unmatched" Comments to Comments table (using Account Name to get
the new ID# from Accounts?)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"NNester" wrote in message
...
I have an Accounts table which contains customer/lead info. Many times
I
receive new names (& complete contact info) in Excel worksheets. I've
tried
everything I can think of to append the new records into my Accounts
table,
i.e. Get External Data/Import to an existing table; Import to a new
table,
then running an append query to the Accounts table. Nothing works! I
get
en
error message saying the records cannot be appended due to validation
rule
violations.

My Accounts table primary key - AccountID - is autonumber. My Excel
files
don't have an AccountID field. Any help is so much appreciated. Right
now
I'm copying & pasting, which for one or two is ok, but sometimes there
are
hundreds of records. Also, if there are comments in the Excel record,
I
need
comments to be appended into a separate Comments table, but tied to the
Account record they belong to.

Can somebody help me?
--
TIA, Nancy