Need help with key violations

EmileEA

Registered User.
Local time
Today, 23:51
Joined
Apr 7, 2014
Messages
17
Hi all,

I found numerous threads online about my problem but the I'm very new to access and therefore it's hard to understand.

I'm having the following problem with my Access Database.

Microsoft Access set 0 fields to Null due to a type of conversion failure, and it didn't add 15 records to the table due to key violations, 0 records due lock violations, and 0 records due to validation rule violations. Do you want to run query anyway?

I'm using Acces 2013.
Normally I append this query every week without problems untill this week.

Can anyone please help me explain in baby language how this problem occurs and how to resolve it without losing data.

Many Thanks,

Emile
 
FYI

Here is MY SQL for the to append query

INSERT INTO EUREKAList ( [Item Number], [Item Title], Platform )
SELECT WeekEUREKA.Itemnumber, WeekEUREKA.Title, WeekEUREKA.Platform
FROM WeekEUREKA LEFT JOIN EUREKAList ON WeekEUREKA.Itemnumber = EUREKAList.[Item Number]
WHERE (((EUREKAList.[Item Number]) Is Null));
 
If you are trying to append data to fields which do not allow duplicates and some of the records you are trying to append are duplicates- you will get a key violation.

Which fields in the destination table do you have set to No Duplicates?

Right click the destination table, click design view. Check each field and see how many have Indexed: Yes (No Duplicates)
 
Hi SmallTomato,

First of all, thanks for your quick reply!

All of the fields have Indexed: No

Is there an easy way to trace those duplicates?

If you are trying to append data to fields which do not allow duplicates and some of the records you are trying to append are duplicates- you will get a key violation.

Which fields in the destination table do you have set to No Duplicates?

Right click the destination table, click design view. Check each field and see how many have Indexed: Yes (No Duplicates)
 
You say they all have Index: No, do you have a primary key in the table? What is the primary key?

First we have to know what would give the key violation before we can know what data is causing it.
 
Yes I have a primary key in the table for the Item Number

You say they all have Index: No, do you have a primary key in the table? What is the primary key?

First we have to know what would give the key violation before we can know what data is causing it.
 
The primary key will not allow duplicates, are you trying to append item numbers that already exist in the table?
 
Each record in your table should be unique. What identifies each record in your table?
 
Yes I'm using a append query to archive weekly sales data to the whole database, therefore some products(Item Numbers) allready exist in the destination table.

The primary key will not allow duplicates, are you trying to append item numbers that already exist in the table?
 
Primary keys have to be unique for each record and are used to uniquely identify each record. If you try to append data that is duplicated into the primary key field, you will get a key violation.

If item numbers are repeated, then it should not be set as the primary key for the table because it does not uniquely identify each record.
 
The thing is I use the AppendQuery to get the new (Item numbers), never archived before products out of it, so it can be placed in a special list with all products. When I click the AppendQuery it first gives me you are about to append 23 rows (23 new products to a 19K long list) after that I get the Key Violation for 15 records.

Which makes me wonder because since it's a new item how could it be a duplicate?

I'm sorry if I'm not making sence, I'm just very new to this and it's the first time in 7 weeks that I have this problem

Primary keys have to be unique for each record and are used to uniquely identify each record. If you try to append data that is duplicated into the primary key field, you will get a key violation.

If item numbers are repeated, then it should not be set as the primary key for the table because it does not uniquely identify each record.
 
Sorry, I did not look at the SQL you posted until now.

Click design on the append query and then click datasheet view so you can see the records it is trying to append.

Does the append query contain duplicate Item Numbers?

Looks to me, if WeekEUREKA contains the same new item number multiple times, it would give this error when trying to append.
 
Last edited:
Yes it does! It finds 16 records with the same itemnumber on the same platform(GameConsole).

Sorry, I did not look at the SQL you posted until now.

Click design on the append query and then click datasheet view so you can see the records it is trying to append.

Does the append query itself contain duplicate Item Numbers? Looks to me, if WeekEUREKA contains a new item number multiple times, it would give this error when trying to append.
 
Yes it does! It finds 16 records with the same itemnumber on the same platform(GameConsole).
This is the reason, it will append it the first time, then give a key violation each other time it tries to append. :)
 
Im very happy that you have helped me find the problem!:D Manyyyyyy Thanks!!

But now my final question, can I ignore this error, since the new title will only be added this week?

This is the reason, it will append it the first time, then give a key violation each other time it tries to append. :)
 
Im very happy that you have helped me find the problem!:D Manyyyyyy Thanks!!

But now my final question, can I ignore this error, since the new title will only be added this week?
I am not the best person to ask about proper design or potential problems down the road :).

Are WeekEUREKA.Title, WeekEUREKA.Platform the same for all 16 duplicate entries?

It will only append the FIRST one... so it will grab that Title and Platform and put it in the table. If you are fine with this, it should be okay.
 
I understand it's just that the person who is the expert on this matter is on vacation so I'm the only person who knows a little bit of it

Yes they are indeed all the same

I am not the best person to ask about proper design or potential problems down the road :).

Are WeekEUREKA.Itemnumber, WeekEUREKA.Title, WeekEUREKA.Platform the same for all 16 duplicate entries?

It will only append the FIRST one... so it will grab that Title and Platform and put it in the table. If you are fine with this, it should be okay.
 
I understand it's just that the person who is the expert on this matter is on vacation so I'm the only person who knows a little bit of it

Yes they are indeed all the same
Then, in my opinion, it should be fine to ignore it.

But if someone with more experience comes in and says otherwise, follow their advice :p.
 
Thanks a lot BigTomato! I will eat a tomato tonight to honour the help you have provided me with! You saved my day!

Then, in my opinion, it should be fine to ignore it.

But if someone with more experience comes in and says otherwise, follow their advice :p.
 

Users who are viewing this thread

Back
Top Bottom