Append query issue

[Jen0dorf]

Hi

I have two table which appear to be identical in format.

Both have a key field called BaptismID

I need to append from table 2 into table 1

I set up an append query by creating the query using table 2 and then selecting the append button and selecting as a destination table 1

INSERT INTO tbl_Baptism ( ChurchID_fk, FicheNo, BirthDate, DateOfBaptism, YearOfBaptism, FullDateOfBaptism, ChildsName, Surname, Sex, Parents, Abode, Occupation, RefNo, PageNo, EntryNo, Minister, Notes )
SELECT tbl_Baptism1.ChurchID_fk, tbl_Baptism1.FicheNo, tbl_Baptism1.BirthDate, tbl_Baptism1.DateOfBaptism, tbl_Baptism1.YearOfBaptism, tbl_Baptism1.FullDateOfBaptism, tbl_Baptism1.ChildsName, tbl_Baptism1.Surname, tbl_Baptism1.Sex, tbl_Baptism1.Parents, tbl_Baptism1.Abode, tbl_Baptism1.Occupation, tbl_Baptism1.RefNo, tbl_Baptism1.PageNo, tbl_Baptism1.EntryNo, tbl_Baptism1.Minister, tbl_Baptism1.Notes
FROM tbl_Baptism1;

When I run is I get a key violation error but the only key I can see is one the field BaptismID which I have not includes in the query

Am I missing some thing

thanks

IanDatabase2.zip

[ranman256]

You cannot add BaptismID if it is autoNum.
you cannot add BaptismID if the BaptismID already exists in target table.

[Jen0dorf]

Hi

thanks for the response but as usual I am confuses

BaptismId is an autonumber and unique and exists in both tables But I have not included that value/field in my query. Does access look at all fields whether included in the query or not?

thanks

Ian

[ranman256]

No, if its not part of the query, then that's not it.
if you have any other fields, INDEXED,NO DUPLICATES
this will fail too.

[Jen0dorf]

Hi

I've been through both tables and only BaptismID is indexed and marked no duplicates.

All the other fields are not indexed and allow duplicates

Strange, not sure where to go now. Is there a way of comparing two tables proporties?

thanks

Ian

[John_G]

You could also check for fields that have Required = Yes in tbl_Baptism, but which are Null in tbl_Baptism1.

I just looked at your attachment and that is not the case.

I ran the query just as you had it in your attached database at it worked fine - no errors. I even ran it twice and it still worked.

What is the exact text of the error you get?

[Jen0dorf]

Hi

just copied back the query I created to upload and it ran with no errors

Weird!!!!

Thanks for all the help

Ian