what I would do is somethin along the lines you mention
but rather than do this by record one at a time do a job lot monthly
have your tick box to archive and have 2 queries
1 append to your archive table nice and easy and another to deleted these are all in the qry selection the query above -
now I work in an insurance industry and what I have built is slight different but along some of your lines
I have a tick box once ticked sets the field values to enable=false
and i have another queire that if a set date has passed and I have no response from a cleint ( date field = null) then update this tick box to yes/true and this again sets the data field to enable = false I then have another qry thats runs an a list to see if these should be archived - and i have an archive field - archive date and archive location every so often i would run the process and move the files accordingly , into a archive Database as well
so after a while you would have an archive table and also an archive D/b
reason for this is my d/b will get pretty full I envisage 250k records and probably only 50K would need to be kept "live" the rest would be enquiries , however I would still need to keep track of them so by having a archive table and an archive D/base i would keep say 3 months worth of enquires archived on my D/base but after this I would move them from Archivetble to archive D/base
I have deviated from what you ahve aqsked but mayber another train of thought might be helpful and sods law as soon as you delete it some muppet would ask about an enquiry 2 years ago or event 10 mins ago
gary
