Append query to parent and child tables

[jchrischinn]

Okay, I've done the research and haven't found the answer yet. This is probably very easy but then I've only been working with Access for about a year.

I have an Access 2003 database that has two primary tables with a parent-child relationship (1 to many) with "enforce referential integrity" turned on. What I'm trying to do is archive the parent records queried with a form and then have all the child records for each parent also archived. I have set up two tables to act as archive tables for the parent-child tables. So basically, I want to copy the queried parent records and all their children to the archive tables and then delete the same records from the original tables.

I've tried using the append query but I haven't figured out how to do that with parent-child tables.

Any suggestions or solutions would be welcomed.

Thanks and have a great day.

 

[WayPay]

0. BACK UP YOUR DATABASE.
1. Copy the parent records to their archive table.
2. Copy the child records to their archive table.

You did check if all went well so far, right?

3. If you have an ON DELETE CASCADE rule, delete the original parent records. Otherwise, Delete the original child records, then delete the parent records.

 

[jchrischinn]

Thanks for the response WayPay. I guess I didn't make my description of the requirement clear enough. What I want is a way so that the user can archive records based on a date range they enter into a form. The user then clicks the command button that runs the append query based on the date range the user entered, and then I want all the parent records that match the date range, and their associated child records, appended to the archive tables. The date range is used to query the parent records (that works right now) but I want the child records appended to the child archive table.

 

[WayPay]

Something like this:

" INSERT INTO ChildArchive (foo, bar, baz) " & _
" SELECT ChildTable.foo, ChildTable.bar, ChildTable.baz " & _
" FROM ParentTable INNER JOIN ChildTable ON ParentTable.ID = ChildTable.ParentID " & _
" WHERE ParentTable.Date " & _
" BETWEEN #" & Me.UserDateField1 & "# AND #" & Me.UserDateField2 & "#"