Append query key violation

[Paintballlovr]

I'm looking for some help with an append query. I am trying to append unmatched records to a table and I keep getting the key violation message. I am not appending to a PK, there are no blank fields in the data I'm trying to append, I have removed any relationships between tables and it still won't work. The most confusing part is if I keep running the query over and over (saying no each time to the message so no appends actually happen), I sometimes get a different count on the number of records with a key violation and if I'm persistent enough, sometimes it will finally run without the error.



This is a large, partially (I've added to it) inherited database, so I'm hoping to not have to make wholesale changes to tables to get this to work. Any ideas?

[ssanfu]

Open the table in design view and check to see if any fields have an index set.

In A2000, there is a button in the toolbar that looks like a stack of pages next to a lightening bolt. Clicking on the button opens a dialog window with all of the indexed listed.

Or you can go through each of the fields to see if there is an index set.

[Paintballlovr]

I Should've included that I'm running 2007, so not sure if that has any bearing on the issue or not. I would've bet my paycheck that I had already adjusted all of the indexes to no, but when I went back and checked again, I did find a couple. Just for reference, I'm just checking/adjusting the common fields, not fields I'm not appending to. Once those were adjusted, I tried the append again, with 5 records to go over. I stopped at 9 attempts this time, with the following counts of key violations:3,1,4,5,4,2,4,5,5

[ssanfu]

You are working on a copy of your dB.... right??

How many fields are being appended?

To find out which field(s) cause the error, start with one field in the query. If that works, add another field and execute the query. Keep adding fields until you get an error. Fix that, then add another field until you have all of the fields being appended without errors.

Are any of the fields you are appending foreign keys that don't have a matching PK in another table? (clutching at straws)

You said the dB is large, so I'm guessing you wouldn't want to provide it for analysis....

[Paintballlovr]

It's not a copy, but it's not live yet either. I'm trying to append 9 fields. I didn't see that any of my fields I'm trying to append are FK. I'll try the one field at a time method and see what I get. I did notice that two of the append to fields showed up in brackets [] when I built the query, which I thought was odd, but I'm not experienced enough to know if it is an issue (probably is). I just removed the brackets from those two fields. The nature of the database is medical billing information, so posting it might cause a HIPAA issue

[ssanfu]

Yes, I deal with the HIPPA issue every day.

with the following counts of key violations:3,1,4,5,4,2,4,5,5

Looking at the above, it sure looks like these are IDs (PK or FK). What data/fields would you have that reflect numbers like this?
Change the append query to a select query (temporarily) to see what data is trying to be appended.

You could change the sensitive data.... and have a small subset of data... but I understand

[Paintballlovr]

qryAPPEND tblMaster_EFT _ tblMaster_Assign.zip

Those counts aren't reflective of the actual data, those are just record counts....BUT I was thinking this morning and I'm wondering if this situation might be an issue. One of the fields I'm trying to append has data that could match on a PK, but I'm not appending to that field. So the example would be in the table I'm trying to append to: record one, field one is a primary key and the data is 12345. In my append data, record one, field eight has data 12345. Would the system generate a key violation on matching data, but different fields? I'm thinking no, but I'm obviously no expert on this subject.

As far as sensitive data, I'm not housing patient information, so it's probably not an issue but I'm new to the industry and I'm not very experienced yet to know if it would be a violation or not. I'm attaching a zip of the records when I switched the query to select. The 835_ID field is the one I referenced above that could match it's data with the primary key in a field called ID.

[ssanfu]

Would the system generate a key violation on matching data, but different fields?

Only if the field names were the same. The order of the fields doesn't matter, but the field names must match.

Without seeing the dB, I'm at a loss as to what to do next.....

How did the one field at a time append query work?

[Paintballlovr]

that's what confuses me, the one field at a time worked flawlessly all the way through, but as soon as I closed the query and tried to run it again with another set of data it failed again. Due to some other issues that came up today, I may have to redesign and go a different direction anyway (not sure of the outcome yet). Thanks for all of your help and if I find myself back to having to address this issue again, I'll repost.