Append Query Key Violation Error 1

[djswitters]

My apologies in advance as I know similar questions have been asked, but I can't figure this out based on other threads:

I have two joined tables that need to be archived (appended) monthly. For illustration, call them Parent and Child. Parent's PK is an Autonumber, which is joined in a one-to-many with Child (the joined field in the Child table is a number field called RecordID, long integer format).

I'm using two append queries to move the Parent and Child data separatly to tables called ParentArch and ChildArch, respectively. ParentArch table and Child Arch table are also joined as a one-to-many with an Autonumber for ParentArch's PK (joined to a long integer number field RecordID in the ChildArch table).

I get a key violation error message when I run the Child table append query (but of course I always run the Parent append first, the Child append second). I don't have the Autonumber field from Parent included in the append query for the Parent table data; but I must be missing something else. Are the table relationships creating the problem? Any advice is greatly appreciated.

Thanks!

 

[flyover789]

of course you must also include the PK of the parent table in the parentarch table, or you will loose the relation to the childarch FK (RecordID).

also make sure, that the PK Autonumber field in the parent table is always incrementing and never reset to a new startvalue.

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[djswitters]

Thanks for your help. I had in fact omitted the Parent table's PK (Autonumber) in the append query to the ParentArch table; that was the problem.

But now I have a secondary issue: I can run the Parent and Child table append queries succesfully only one time--- when I try to run the same append queries a second time, the Parent query fails due to another key violation.

What I need in my project is to be able to move current data to the archive tables each month. No archived records will ever be completely identical, but I guess they would end up sharing Autonumbers (PK) in the current setup (since the same autonumbers tied to the Parent records will continue trying to archive each month).

Is the second round of append queries causing a key violation because the query is trying to append duplicate Autonumbers into the ParentArch table?

Thanks again, I really appreciate the help.

 

[flyover789]

also make sure, that the PK Autonumber field in the parent table is always incrementing and never reset to a new startvalue.

this advice from my previous post is exacly referring to the problem you are describing now.
the PK in the parentarch has to be unique and the same as in the parent table, or else your design will not work.
I stronlgy recommend you take a look here:

http://r937.com/relational.html

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[willir]

Martin

When I read your original post, I had to wonder if you only appended "once" because of the way you explained your design.

...call them Parent and Child. Parent's PK is an Autonumber, which is joined in a one-to-many with Child (the joined field in the Child table is a number field called RecordID...are also joined as a one-to-many with an Autonumber for ParentArch's PK ...

Fly addressed this issue verbally.

Being more visual, one example...

tblParent
ParentID - pk, autonumber
ParentDate
ParentData
....etc

tblChild
ChildID - pk, autonumber
ParentID - foreign key (fk) to tblParent.ParentID
ParentDate
ParentData
...etc

Here, the one-to-many link is kept by the ParentID in tblChild and tblParent. The foreign key is always on the "many" side.

Another example, more complex

tblChild
ParentID - fk
ParentDate
ParentData
...etc

primary key = ParentID + ParentDate.
Using a composite primary key prevents the same record being appended twice to the tblChild but requries a little more work.

And definitley read Fundamentals of Relational Database Design by Paul Litwin.

Richard

 

[djswitters]

Thanks very much to both of you for your help. I just want to make sure I've summarized my issue clearly:

At the end of each month I need to append whatever's in my Parent and Child tables into the ParentArch and ChildArch tables, respectively. I then run an update query on the Parent table to update a Month field/ Year field and second update query to zero-out some data fields in the Child table.

When I run the append queries a second time (after a month has passed from the first run), Access is rightfully preventing me from appending the records to my archive tables since the Autoarchive numbers I'm trying to copy are identical to the Autoarchive numbers already in the archive tables.

Martin, your answer makes sense to me-- why not create a composite PK (which I could do with the addition of my Month and Year columns)-- but doesn't that violate the 2nd Normal form as a relationship? Ideally, I'd like to keep things simple w/ just using an autonumber for my PK if at all possible.

Can I do anything else to change the Autonumbers so that I'm not trying to copy the same Autonumbers over and over into archive, or is a composite PK a better way to go?

Thanks again!

 

[flyover789]

Martin

When I read your original post, I had to wonder if you only appended "once" because of the way you explained your design.
...

Willir: I was not the original poster, djswitters was

Martin, your answer makes sense to me-- why not create a composite PK ...

djswitters: I didn't make this proposal, willir did

okay, now back to the topic:
djswitters:
as you are archiving your tables, why can't you delete the archived records from the original tables completely and start creating new records (and thereby generating new autnumber PKs) after the archiving?
If you have data in your tables that remain the same all the time (so they are static data), I'd recommend to put these data outside of the transactional tables into some "master data" table(s).

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[djswitters]

(Sorry about the name mix-up confusion, fly and willer)

Fly, your last recomendation was on the money-- I don't know why I missed it in the first place. I now have a macro handling the following:
1) Run append queries for the Parent and Child table to move them into their respective archive tables.
2) Run delete query on the Parent table (which deletes in turn any related child data via cascade delete).
3) Run another append query moving my static fields from the Parent archive table back to the Parent table.
4) Run an update query on the Parent table to update Month and Year fields.

Can't thank you enough for your help-- I can finally get on with finishing this project now.