Archiving Data from Multiple Tables (1 Viewer)

[Tina49]

I have a database with employees. The tables are as follows:

Deptdatatble
Depttble
Emptble
HRtble
Servicetble
Servicedatatble
Archivetble
Classestble
Classdatatble

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 and 4 records in the Classdata table, then it exports out 16 records (4 x 4). Why is this? I would expect it to export out 8 records.

 

[Pat Hartman]

You can't include multiple 1-m relationships in a single query unless the relationships are hierarchical. What you are getting is a Cartesian Product. Class and service have no relationship with each other. Just because they are both related to employee doesn't mean they are related to each other.

To solve the problem, create a separate query to export data from each table independently.

 

[Tina49]

I will give that a try Pat. So any type of one to many relationship needs to be exported separately then.
Can I create a query for each one and then put both queries in the append query? Or will I still have the same issue?

 

[Pat Hartman]

So any type of one to many relationship needs to be exported separately then.

They do if you want them to go into separate tables. Sounds like your archive has a different format than the current tables. Make the archive tables have the same format as the current to avoid problems. The only difference is that the primary keys in the archive tables will be defined as long integer rather than autonumber. When you append the rows, you want to maintain the original autonumber values. You don't want to generate new ones.

Edited to correct confusing typo.

 

[Tina49]

I was just going to have them go into one table called archive. I had already created a form that would put the information together so if they needed to see the archived data they could. Perhaps though it would be beneficial to have the information in the original archived tables. I do have 4 tables they would have to answer messages to, or I guess I could suppress the messages.

 

[Pat Hartman]

You can supress the warning messages. I create two macros in all my databases to solve this problem. One turns warnings off and the other turns them back on. I use macros because occassionally I need to run them manually. Because it is sooooo dangerous to turn warnings off, I like a visual reminder so my mWarningsOff macro turns the Hourglass on and my mWarningsOn macro turns the hourglass off. This is useful during debugging should the code stop before runnint the mWarningsOn. If I see that the hourglass is on, I KNOW that mWarningsOn didn't run and I can just go run it.

 

[Tina49]

Moving the files into archived tables worked great! I decided to leave messages on, so that they don't delete archived employees that never officially archived (In case of error). Thanks again for all your advice!