Append Query Key violation

[ellenr3608]

Hello! I am a self taught access user trying to make sense of the true costs of making my jewelry. I am using a template that I found within the access program and rather than "recreate the wheel" I enter any new inventory a few weeks until I started getting this message:

"Microsoft Access set 0 filed(s) to Null due to a type conversion failure, and it didn't add 417 records to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations."

I have been trying to solve this on my own but am stumped. The problem seems to be due to key violations. My Primary key is set to "yes, no duplicates" other columns are set to "Yes(allow duplicates"

Any idea what the problem is?

 

[Gasman]

Just what it tells you. The keys already exist.?

Are you trying to append data that you have previously appended.?

Can you explain the steps you take.?

 

[ellenr3608]

Sorry I am a very green user. Yes I am trying to append data previously append as well as the new data.

What I do is add the new data to my table I created in the template. Then I run the append query and get the error. It would seem to me that the new data would add on to the already existing one. I made the new table as it allows me a little more freedom with what I want to have included but did't want to mess up the original template.

Hope this makes sense and I am not doing something really dense.

 

[Gasman]

You need someway to only add the new data. How would you identify new data in you data entry table.? Alternatively delete all the old data and re append, but I would not do that until we find out exactly what you are doing, else you might lose some data.

I would have thought you would just add new inventory directly to the table through a form, as you have to enter it somewhere anyway.?

Might be worth uploading your DB as a zipped file and then I or anyone else can help better.?

You would still need to explain the exact steps you are taking.?

 

[ellenr3608]

I could provide additional information and would appreciate any help someone could provide. I feel it is something simple but can't seem to find it.

 

[Gasman]

Well it really depends on what you are doing and what you are trying to do.?
I'm confused as to why you are appending.?

If you found a template, that should be what you are using. Data will be entered into it and reported from.? What is the template.? I might be stuck here as I only have 2007, but others have much later versions.

 

[ellenr3608]

The reason that I don't add it to the original table that came with the template is that the table didn't include some things that I wanted (quantity and total for example) and had a bunch more fields than what I needed (name and address of the manufacturer for example) That table is still needed for the other things to work within the template so I left it. If I knew more/felt more comfortable with access I might be able to go back and change things but I was afraid that I would make mess up. It seemed simpler to create the table that I would use to put in all the things I needed to refernece them then append to the templates inventory table to create the invoices as I make each piece of my jewelry.

Here is what I do exactly: When I add new inventory I put it in the table I created, post the price, post the quantity, take a photo. then I run the append query to add it to the template's original table. The error shows up.

I wonder why the new data doesn't come in? I even tried erasing some data in the template's inventory table to see if it would reinsert the missing stuff but nope it doesn't.

Is there some way I can tell it (the query) to just fill in what isn't already there (new data)?

 

[Gasman]

Ok. You will need to identify what the key is, likely an autonumber field.?

However when you append, you should not be referencing the autonumber field. That will get completed as the records are added. Are you doing that.?

This is going to be a mighty long thread unless you can upload the DB?

 

[arnelgp]

use update query to append new record and update old record:

update tbl2update as t1 RIGHT JOIN
tblCommingUpdate As t2 on t1.pkfield=t2.pkfield Set t1.pkfield=t2.pkfield,
t1.otherfield1=t2.otherfield1,
t1.otherfield2=t2.otherfield2;

 

[ellenr3608]

I would love to upload it but first have to figure out how to zip it up.....used to do that all the time as I had a program that did it at work before I retired. Let me study on this and also maybe try and update query. Both of you are being so helpful and I am all thumbs....


Arnelgp I am assuming what you wrote is the code I should l use substituting the field and table names?

Here is my code for the append:
INSERT INTO Inventory ( ID, Item, price )
SELECT [Inventory Ellen made].ID, [Inventory Ellen made].Item, [Inventory Ellen made].price
FROM [Inventory Ellen made];

 

[ellenr3608]

Opps the file is still too large for the server.

 

[arnelgp]

UPDATE Inventory AS A
RIGHT JOIN [Inventory Ellen made] AS B
ON A.ID=B.ID SET A.ID=B.ID,
A.Item=B.Item, A.price=B.price;

 

[Gasman]

I would love to upload it but first have to figure out how to zip it up.....used to do that all the time as I had a program that did it at work before I retired. Let me study on this and also maybe try and update query. Both of you are being so helpful and I am all thumbs....


Arnelgp I am assuming what you wrote is the code I should l use substituting the field and table names?

Here is my code for the append:
INSERT INTO Inventory ( ID, Item, price )
SELECT [Inventory Ellen made].ID, [Inventory Ellen made].Item, [Inventory Ellen made].price
FROM [Inventory Ellen made];

No, leave the ID out, that is probably your problem.? You will have the same IDs in both tables, even though they are not related.

To zip a file these days, right click and Send To/Compressed (zipped) Folder. Compact first.

 

[ellenr3608]

No, leave the ID out, that is probably your problem.? You will have the same IDs in both tables, even though they are not related.

To zip a file these days, right click and Send To/Compressed (zipped) Folder. Compact first.

So just create another Query with just the item and the price....Let me try that

 

[Gasman]

So just create another Query with just the item and the price....Let me try that

Make sure you only append the new records.?

 

[ellenr3608]

nope----just added everything a second time. I have a back up though!

 

[ellenr3608]

How do I just append the new records?

 

[Gasman]

Are the IDs the same in each table?

 

[arnelgp]

if ID is autonumber on both table then that would be the problem. create new column (itemCode) to better ident your item rather than autonums.

 

[ellenr3608]

Are the IDs the same in each table?

yes