Append Query for Archiving

[Canadiangal]

I have a database with the following tables:

DepartmentData
Department
DepartmentTitles
Attendance
Employees
HR
MeetingsClasses
PatientTreatmentProgram
Procedures

I have a field called status that marks employees as non-employees. However, at certain times, I want to archive employees out (lets say they are terminated). When I do this, something strange happens. If an employee has 4 records in the servicedata table (They may have had 4 services, in this case a service can be a flu shot, health assessment, etc.) and 4 records in the Classdata table (They may have attended 4 different required continuing education classes), then it exports out 16 records (4 x 4). I understand this is happening because PatientTreatmentprogram and Classdata table have no relationship. A Cartesian Product I am told. Would I be better off creating separate archive tables and archiving the data back into separate tables? Any words of wisdom? And no I can't simply marked them as archived and leave them. That isn't what the departments heads have requested....

[June7]

Nothing strange at all about those results. That is to be expected when including multiple 'many' tables in a query.

Department heads don't understand relational database.

Why do the department heads even know how the data is structured? If the forms and reports filter out the 'inactive' records, the result is the same as 'archiving'. Archiving will give you endless headaches because of related records, especially with multiple 1-to-many relationships, as you are seeing. Duplicate 'archive' tables would be the only way and if the pk/fk fields are based on autonumber then would require very complex code to accomplish.

[Canadiangal]

The primary field that links the majority of the tables together is an auto number. Could I simply have the archived table be a number field, so that when it appends it just gives it the current auto number? I will give this a try and see what happens.