key violations

[Jen0dorf]

Hi



I have two tables with identical field names and field proporties -

One table has field BaptismID which is an auto number and a Primary Key, table 2 does not have this field.

I need to append table 2 to table 1

Using the query below

INSERT INTO tbl_Baptism ( ChurchId_fk, FicheNo, PageNo, BirthDate, DateOfBaptism, YearOfBaptism, FullDateOfBaptism, ChildsName, Surname, Sex, Parents, Abode, Occupation, Minister, Notes )
SELECT [Baptisms 1813-1914].ChurchId_fk, [Baptisms 1813-1914].FicheNo, [Baptisms 1813-1914].PageNo, [Baptisms 1813-1914].BirthDate, [Baptisms 1813-1914].DateOfBaptism, [Baptisms 1813-1914].YearOfBaptism, [Baptisms 1813-1914].FullDateOfBaptism, [Baptisms 1813-1914].ChildsName, [Baptisms 1813-1914].Surname, [Baptisms 1813-1914].Sex, [Baptisms 1813-1914].Parents, [Baptisms 1813-1914].Abode, [Baptisms 1813-1914].Occupation, [Baptisms 1813-1914].Minister, [Baptisms 1813-1914].Notes
FROM [Baptisms 1813-1914];

I get the error message "due to table key violations"

What have I missed? How do I trouble shoot this error?

thanks

Ian

[CJ_London]

the message implies you have set a field to not allow duplicates in your destination table

[CJ_London]

or in your relationships you have a one to many - perhaps the churchFK for one or more records does not have a corresponding record/value in the tblChurches

[Jen0dorf]

Hi

As usual really appreciate the help, you were correct in that if I remove the ChurchId_Fk field from the query it all works perfectly.

If I then create another query to update the ChurchID_fk field to 7 - which is the value from my churches table I again get the key error.

So How do I append the records from
Baptisms 1813-1914 to tblBaptisms?

tblchurch has a one to many with tblBaptism and I have manually added the entry in tblChurch setting the churchID to 7

Each parish can have multiple churches and each church can have multiple baptisms

thanks

Ian

[CJ_London]

did you see my second post? are all the churches identified by churchfk in tbl_church? If not, you need to add them in before running your query

Easy way to find out - run this query - if it comes back with any records, then the ChurchId_fk either needs to be updated to a church that does exist, or add the church into tbl_Church and then update ChurchId_fk

SELECT  [Baptisms 1813-1914].*
FROM [Baptisms 1813-1914] LEFT JOIN tbl_Church ON [Baptisms 1813-1914].ChurchId_fk =  tbl_Church.ChurchID
WHERE  tbl_Church.ChurchID is null

[Jen0dorf]

Hi

many thanks, I did see your second post and checked and it appeared ok.

However when I ran your query it showed records so I went back and checked again and realised I had used the wrong church Id.

Note to self - always make a list!

cheers

Ian