Delete, Append, Make New Table Querries....what???

[attrofy]

I have a databse that tracks collection activity. It consists of 3 tables (tblCustomer, tblCollection, and tblAmount). I use one form to enter data, and a second form (based on a querry) to append and delete the accounts. We would like to be able to track statistical info over the course of the year (quarterly, year end etc) as well as find out if the same accounts are coming up for collection repeatedly. I created an append querry that will store the info in a fourth table (tblArchive). Every time I run the querry, it adds all the current info, even if it is a duplicate. If I cahnge the querry to a MakeTable querry, it deletes the table every time it runs. If I leave the querry as an append querry, then create a find duplicates querry, I get a 2nd table with all the dups. If I convert this to a delete querry, it takes out all of the dups, and the originals, thereby leaving me with only the single entries - which are really all the deleted accounts.
How can I make a table that will record all of the accounts, regardless of if they get deleted. Kind of a snapshot of every account that has been entered throughout the year. The next part is how do I get it to run automatically so that all activity will be recorded without relying on the human factor???
I know I am close, but missing something....thanks for the help.

 

[new9]

Hi,
Yuo wrote that "even if it is a duplicate",so maybe you did not set up a primary key for your forth table.
Why do not you try to creat a macro to run your append query then run it each time yor delete a record(Assument that yor forth table will hold deleted record only). Then, when you need to get your statistic, create select query based on your master table and your forth table.

Hope this will help

 

[attrofy]

Thanks for the quick response. I see where you are going with this, but we need to be able to track duplicates. This is info based on on going collection activity. If we have an account that appears more than once for collections, we need to be made aware of that. Also, the idea is to try to store all records so at the end of the year we can run a report to tell us that we had $X of outstanding debt for the year, and we recovered 95% before sending it to collections agencies. The only way I can think of generating that kind of info, is to store a record of every collection account that goes through the system.
The problem comes in with the fact that once a record is no longer being actively persued for collections, it gets deleted. Do I need to reconsider the way I have my tables created? Should I try to create a seperate table at all, or do I just need to run a querry to store all accounts once a user deletes a record? If so, how do I circumvent the same problem I am having now?
Any ideas????