Append Query Key violation (1 Viewer)

[arnelgp]

INSERT INTO Inventory (Item, price )
SELECT [Inventory Ellen made].Item, [Inventory Ellen made].price
FROM [Inventory Ellen made]
WHERE [Inventory Ellen made].Item
Not In (SELECT Item FROM Inventory);

 

[arnelgp]

is Id autonumber?

 

[Gasman]

Ok, arnelgp will sort you out, but I would have thought it would along the lines of

And arnel beat me to it.

 

[arnelgp]

you go ahead mr.g, i am going to sleep now.
iam 11 hrs ahead of you.

 

[Gasman]

you go ahead mr.g, i am going to sleep now.
iam 11 hrs ahead of you.

You are always way ahead of me arnel.

 

[ellenr3608]

Tried substituting the code Arnel wrote into the Query and it says you are about to append 0 rows. then nothing changes

 

[Gasman]

We are going to be here all day, doing it like this.

Take a copy, reduce the data in the copy, zip and upload.

We need everything you need to replicate the problem, but only a few records are needed?

What Access are you using as mine with not work with later versions sometimes, but someone else will still be able to help.

 

[ellenr3608]

I am sorry to be so much trouble. Maybe I could just upload the two tables and the query?

 

[Gasman]

I am sorry to be so much trouble. Maybe I could just upload the two tables and the query?

I did not mean it like that, just that to and fro is not going to work.?

Upload whatever we need, with instructions on the steps to take.?

 

[ellenr3608]

Tried deleting just the unused files but still too big. Going for just the two tables and query

 

[ellenr3608]

Geez! How small does it have to be? The file is 868,000 kb

 

[Gasman]

Geez! How small does it have to be? The file is 868,000 kb

You need to zip it? Does it have any attachments in it.?

 

[ellenr3608]

Did zip it. Deleted all the photos I had in my inventory and it worked!

 

[Gasman]

Did zip it. Deleted all the photos I had in my inventory and it worked!

Yes, just put links to the photos and put your photos in a folder with the DB, else you quickly run out of space.

Here is hoping I can open it, as you did not say what version you have?

Also have the seen the size zipped when you have done that?

 

[ellenr3608]

Yes, just put links to the photos and put your photos in a folder with the DB, else you quickly run out of space.

Here is hoping I can open it, as you did not say what version you have?

Also have the seen the size zipped when you have done that?

I have office 360. I am assuming its the same.

 

[Gasman]

OK, before I go any further, you have the same ids in both tables?, so it is never going to append?

Delete a few records from the target table and try again.?

Use the Small DB you uploaded as you are more au fait with the DB. If that fails I will look further. FWIW I was able to open it.

 

[Gasman]

OK, this works

INSERT INTO Inventory ( Item, price )
SELECT [Inventory Ellen made].Item, [Inventory Ellen made].price
FROM [Inventory Ellen made]
WHERE [Inventory Ellen made].ID NOT IN (Select ID from Inventory)

 

[ellenr3608]

OMG IT WORKED!!!!!!!!!!!!!!

 

[ellenr3608]

OMG IT WORKED!!!!!!!!!!!!!!

all I did was erase the bottom 10 and then ran it and it worked!

 

[Gasman]

You need to take a step back an understand what and how it all works.

FWIW I think you are just making work for yourself, especially with the lack of experience with Access.

Your next struggle is going to be updating the Inventory from the one you made.?

I believe you should just use the template. ignore irrevelant fields unless they insist on having a vlaue. Even then you could amend the DB so they do not.

Good luck with it anyway.