How to save or transfer deleted records to another table (1 Viewer)

[missy]

Hi,

I am new to the forum so not sure if i am posting this in the right section so apologies if I'm not in the right section. I am doing up a small database for a friend as a favour and think it seems pretty okay.But I am stuck on the following problem and what is the best way to go about it.

I need to delete customers but want to hold their details for about 6 months (in case of dispute or follow up) and if possible to automatically delete after 6 months. So was thinking along the lines of current customer table and then a previous customer table to hold the deleted details. Is Append table query the best way to go about this and how.

Thanking you in advance for any help

 

[pbaldy]

The "best" way is generally not to delete them at all, or even move them to an archive table. Typically it's best to simply have a status field of some sort, which enables you to easily query current, "deleted" or both.

 

[missy]

Thanks for them ideas - didn't even think of doing that way. How would i go about creating an archive table and how to move the records to the archive table. I havent done any of that before, i'm afraid my access knowledge is basic enough. In your expert opinion do you think this is the best and simplest way to do this or should i create a status field for current or previous customers. Again thanks for your help and great advice

 

[pbaldy]

Barring very unusual circumstances, my first choice would always be to leave the data in one table, with a status field. If you have typical related tables, you might have a sales table that includes a field for customer. If you delete the customer from the customer table, the sales table loses its link to the related customer info.

 

[missy]

Again thanks for your reply.

The customer table has relationships with invoice table and payments table
1-> customer tbl to invoice tbl
1-> customer tbl to payment tbl

when you say a status field - i'm sorry for not understanding but do you mean a drop down box and select current or previous, or a box to tick for current and box to tick for previous customer. I'm sorry i'm not grasping your concept. Just cant seem to get my head round on how to get it working correctly.

 

[pbaldy]

It could be either, depending on your specific needs. If it's simply current or previous, I'd probably have a Yes/No field (represented by a checkbox on your forms). I would only have one field, not one for each possibility. Checked would mean previous, unchecked would mean current (or vice-versa, as you choose).

I've seen HR systems that had A for active, T for terminated, L for leave of absence, etc. For that I'd use a combo so the user could only pick a valid choice.

 

[missy]

Thanks i'm finally getting the picture. I will go with the yes/no option its all been great advice. I have most of my queries done - was putting this little problem on the backburner until i got things working okay and said i'd come back to it.

Does this mean i will need to amend relevant queries that i think might be affected by this, to only include current tenants.

Again apologies think the brain is getting a bit burnt out this late.

 

[pbaldy]

Likely yes. Another advantage of one table is that you can easily query for current, previous or all customers just by using the appropriate criteria. The downside is that since you didn't start this way, any queries that were just supposed to return current customers will have to have a criteria added to them.

What do you mean late? It's still afternoon!

My daughter lives in England, so I know you're burning the midnight oil there. Go to bed...or a pub!

 

[missy]

I knew in my heart i'd have to but was just hoping that you'd wave a magic wand and I wouldn't have to amend them. You are so right about keeping them in the one table and keeping it simple - I was really overthinking the problem and making it more complicated that it was.

Think I'll go with your first suggestion to go to bed before i totally wreck your head. would love to hit the pub but sadly those nights are rare with kids and being a mature student - the joys of it all

Hope you have a good day - you've done your good deed for the day helping me out. Thanks