Diagnosing Key Violations Error

[NBVC]

I am wondering how I can diagnose a key violations error when I try to perform an Append Query...

See attached for exact error that I get.

I am not sure where/what to look for.

I found in another thread something about deleting AutoIndex or Import/Create field from the Tools|Options menu... but that didn't fix anything....

Thanks for any assistance.

This is the sql code for the query:

INSERT INTO SYSADM_MARKET_PRICE ( PART_ID, MARKET_ID, SELLING_UM, DEFAULT_UNIT_PRICE, CURRENCY_ID, QTY_BREAK_1, QTY_BREAK_2, QTY_BREAK_3, QTY_BREAK_4, QTY_BREAK_5, QTY_BREAK_6, QTY_BREAK_7, QTY_BREAK_8, QTY_BREAK_9, QTY_BREAK_10, UNIT_PRICE_1, UNIT_PRICE_2, UNIT_PRICE_3, UNIT_PRICE_4, UNIT_PRICE_5, UNIT_PRICE_6, UNIT_PRICE_7, UNIT_PRICE_8, UNIT_PRICE_9, UNIT_PRICE_10 )
SELECT Tbl010_NewMarketPrice.PART_ID, Tbl010_NewMarketPrice.MARKET_ID, Tbl010_NewMarketPrice.SELLING_UM, Tbl010_NewMarketPrice.DEFAULT_UNIT_PRICE, Tbl010_NewMarketPrice.CURRENCY_ID, Tbl010_NewMarketPrice.QTY_BREAK_1, Tbl010_NewMarketPrice.QTY_BREAK_2, Tbl010_NewMarketPrice.QTY_BREAK_3, Tbl010_NewMarketPrice.QTY_BREAK_4, Tbl010_NewMarketPrice.QTY_BREAK_5, Tbl010_NewMarketPrice.QTY_BREAK_6, Tbl010_NewMarketPrice.QTY_BREAK_7, Tbl010_NewMarketPrice.QTY_BREAK_8, Tbl010_NewMarketPrice.QTY_BREAK_9, Tbl010_NewMarketPrice.QTY_BREAK_10, Tbl010_NewMarketPrice.UNIT_PRICE_1, Tbl010_NewMarketPrice.UNIT_PRICE_2, Tbl010_NewMarketPrice.UNIT_PRICE_3, Tbl010_NewMarketPrice.UNIT_PRICE_4, Tbl010_NewMarketPrice.UNIT_PRICE_5, Tbl010_NewMarketPrice.UNIT_PRICE_6, Tbl010_NewMarketPrice.UNIT_PRICE_7, Tbl010_NewMarketPrice.UNIT_PRICE_8, Tbl010_NewMarketPrice.UNIT_PRICE_9, Tbl010_NewMarketPrice.UNIT_PRICE_10
FROM Tbl010_NewMarketPrice;

 

[Galaxiom]

You would probably be trying to append a record with a [PART ID] that already exists in the destination table.

Do a select query with an inner join in the [Part_ID] between the source and destination tables.

BTW. Suggest you drop the underscore from your object and field names. They don't help with readability and Access uses them for itself in VBA.

Also suggest you stop fully capitalising object and field names. This makes it harder to read. Capitalise the commands and the first letter of object name words

 

[NBVC]

SYSADM_MARKET_PRICE is an empty table that I am trying to append to. It is a linked table to our ERP database.

When I try appending (transferring) the items from Tbl010_NewMarketPrice table, I get that error message.

I tried the query with Innerjoin and got an empty result.

The Underscores and Capitalization is defined by our database.. I cannot change them.

 

[gemma-the-husky]

the most likely possibility is that the records you are trying to add, are failing because they are creating duplicate keys

so have a look at your keys in the blank table, and see if one of those might be the problem.


---------
The other thing is that if this table is related to another table - then you may not be able to apend records because there is no related reocrd ... and I think you will get the same error.

 

[NBVC]

So when I look at the SYSADM_MARKET_PRICE table in design view.

I see a key beside PART_ID, MARKET_ID and CURRENCY.

Are you saying to look if any of these 3 are duplicated?

Do they have to be duplicate in all 3 fields to trigger that error?

What if I have PART_ID duplicated, but with different MARKET_ID and/or CURRENCY, does that trigger the error?

 

[Galaxiom]

That is a Composite Key.
All three fields of an existing record would need to be duplicated by the problem appended record for it to be rejected.

 

[gemma-the-husky]

those 3 fields together form your primary key - so the set of those values

a) has to be unique
b) and no part of that key can be blank

------
but you may have other keys in the table, besides the primary key.

 

[NBVC]

Thank you for those replies.. Looks like that might be the case here... I will have to remove some duplicates...

Out of curiousity... if I was to ignore the error and accept the fact the 608 records would not be appended... what records would it append or not append?

So if I have 3 duplicates in those keys and the rest of the fields might not be duplicates... would it take the first record or the last record or remove all 3?

 

[gemma-the-husky]

random

no saying which records would be rejected, i dont think

on the basis that in a DBS all records are of equivalent importance.

most likely it would store the first one it came across

 

[NBVC]

Ok thank you for your assistance.

 

[NBVC]

I have cleaned up the database and have no duplicates of those 3 keys....

... but I still get the same error.. only it is reduced to 104 key violations...

What else do I need to look for?

 

[NBVC]

So any more ideas on how I can find out what these 104 records are?

 

[gemma-the-husky]

how many records do you have altogether?

try a totals query

include the 3 fields that make up the key
put group by against these

include another field in the query (any)
put count for this, instead of groupby

in the criteria for the count, at the bottom put >1

this will show you the duplicates.

 

[NBVC]

I get an empty table...

I also already pre-confirmed that there were no dupes by exporting the table to excel and testing for duplicates amongst those 3 keys.

 

[gemma-the-husky]

ok

if you imported the records and not all could be saved, then obviously you dont now have any duplicates - but there amy have been duplicates originally (you mention 688 at first - then 104)

now - as i say the other reason for problems is if this table has relationships with other tables - and you dont have the requsite item in those tables - nothing to do with the 3-key set up

lets say each customer has to be allocated to a rep - but one of the reps is missing - then you cant create the customer - because of this - and I think you get the same error

 

[NBVC]

I don't think that is the case here.

All the columns (key columns and other) that have required fields are filled in...

There are other columns with many blanks, but they are all not required fields.

If, again, I export to Excel and then count blanks in each column... I get 0 blanks in my key columns and the other 2 that are "required".. and it the fields that have not required items, i have different numbers.. but none add up to 104, which is the number of key violations I am getting....

Edit: I attached a screen shot of the Design View of the Query and the 2 tables in questions.. in case that might help.

 

[gemma-the-husky]

right

you have a table with x items in, called table 10 etc. How many records does this have?
you are trying to insert these into a similar table, but some are being rejectd. How many are rejected?


now, do all this, just on this table

first make a query based on this table
put in it columns for

market_id, part_id and currency_id - your key fields, and another column for part_id

----------
now

first sort a-z on each of the first three columns in turn.
if there are any nulls/blanks then these will definitley present a problem.

now
try making this query a totals query
in each of the first 3 columns leave it as group by
in the 4th column, change groupby to count, and in the criteria put >1
this will show you any duplicates

now close the query
open the table in design mode
grab the field currency id, and move it so its in the top3 places

now highlight the first 3 fields - ie market_id, part_id and currency_id and try and make this the primary key (click the key symbol). I am SURE access wil tell you it cannot do it, because the key is not unique

================
and that is why you are getting some items rejected.



===============
just one other possibility

if you have required fields, and they are null, then you may not to able to insert the rows into the new table, and it may be this that is causing the problem.

I am not 100% sure, but i think required=true and allow ZLS may be compatible.
however required = true and NULL are definitely not compatible - and you cannot tell by inspection whether a field is null or a zls

=================

all i can say is that access is unlikely to make a mistake - there is some uncompatible data in your files, and learning what is causing problems, and solving the problems is all part of the learning process for access.

 

[NBVC]

Thank you for your continued support on this matter...

I have done as you described in making a query and adjusting the table.

There are still not returns on the query and Access did allow me to make those 3 items keys..

There are no blanks or nulls that I can see...

Not sure what zls means?

There are a total of 9248 records in the table and the Append Query is rejecting 104 of them.

I am afraid to let the query continue with the errors because I don't know which are rejected and why?

 

[NBVC]

Okay, so I figured it out...

I took the risk and ran the query with the key violation errors.. then I compared the resultant table with the original table and found that 104 of the PART_IDs did not exist in our database for it to append to.

Why doesn't access have something that can identify that before you actually accept your fate?

Anyway, thanks for your persistance... Appreciated.