can't append records in append query

  • Thread starter Thread starter Greg Clements
  • Start date Start date
G

Greg Clements

Help,

i have 2 tables, one is an import from a check run it has
a field on it for current paid deductions data for that
check, the other table has a field of what that employee
has already paid, i am tring to run an append query to
add the two fields from both table together and then post
it to the table with the amount already paid field on it.
both fields are set to be currency.

i have tried multiple different configurations of the
query and a different type of query (Update)and keep
getting the same meesage no matter what i do the message
is:
Microsoft Access set 0 Field(s) to Null due to a type
conversion failure, and it didn't add 24 records to the
table due to key violations, 0 records due to lock
violations, and 0 records due to validation rule
violations.

if i run the query as a select query the two fields add
together fine here is the basic formula i am using:

Expr1: tblDistribution2.[Payed]+[CHECKVIEWDEDCODE]


this is supposed to go the the feild PayedUpdate on the
tblDistribution2.

any help would be great.

Thanks in advance

Greg
 
Hi Greg,

The error that you are getting has to do with the primary
key for the table. The append query cannot append the
records because it would violate the primary key. Let's
say that your table is keyed by employee number, which
would mean that duplicate employee numbers are not
allowed. If you try to append updated info for an
employee, the table will not take it because a record
already exists in the table and duplicates are not
allowed.

Instead, you should use an update query to do this (which
should not give you the key violation unless you were
also trying to modify the key field, but you should not
be doing this).

Or, if you would like to store historical data (if you
have a need for it), you could change the employee table
to be keyed by the combination of Employee ID and
Effective Date, and then you would append records as the
data changes with the new values and the effective date.
To get the current status for employees you would then
query for the data matching the max effective date for
each employee.

Post back with your table structures (table names and
relevant fields)and your current update query sql if you
would like additional help with the update query syntax.

-Ted Allen
 
Back
Top