How to move old record to another table

[ckleung]

Hi,

I am using Access to develop a database for my old aged home to store the record of the residents. When some residents move out, I do not want to delete the records forever but keep them in another table so that I can retrieve them whenever I need. How to write VBA or marco to achieve the above purpose so that the operator can click a button to complete.

Many thanks for help !

 

[Rich]

Either use append and delete queries to move and delete the original record or just add a flag to the original table and filter the records

 

[dan-cat]

Instead of having 2 tables with exactly the same structure - I would stick to having just one table. Perhaps add a status field to each residentid identifying them as in-house, out-house etc.

You may like to read up on the rules of normalization

 

[Rich]

You may like to read up on the rules of normalization

Actually many programmers use the two table method

 

[dan-cat]

I have heard of this method but was always a bit wary of it.

What would it be used for mainly - archiving?

Say if you are performing a record search - wouldn't you have to clarify to the user which table they are searching?

I'm intrigued as it how this method would improve performance

By the way how is Stadtler? Haven't heard from him for ages

 

[Rich]

By the way how is Stadtler? Haven't heard from him for ages

 

[Mile-O]

Maybe he's implying you are Waldorf.

 

[dan-cat]

I wasn't implying - I was reminding

Mr Phile perhaps you could enlighten me on the merits of using the two table method. I thought this would fly in the face of normalization?

 

[Rich]

I wasn't implying - I was reminding

 

[Mile-O]

Mr Phile perhaps you could enlighten me on the merits of using the two table method. I thought this would fly in the face of normalization?

Er...if you have a big table full of historical records spanning years then it might be beneficial to keep the historic records in the archive table so that querying current/active records is sped up and Jet doesn't choke. Other than that assumption, I don't know.

 

[dan-cat]

See what I mean? His mechanism has gone

 

[ckleung]

It is for archiving

Yes, the main purpose is used for archiving.

Anyway, this is the first time I post question here, and I am very glad to have all of your response so quickly.

Indeed, this is the first database I created. I don't have many experience in it.

 

[dan-cat]

You'll always get a response here - we have nothing better to do than argue about gun culture and how young people are so unruly these days

 

[Rich]

See what I mean? His mechanism has gone

 

[John liem]

Archiving historical records

Er...if you have a big table full of historical records spanning years then it might be beneficial to keep the historic records in the archive table so that querying current/active records is sped up and Jet doesn't choke. Other than that assumption, I don't know.

Hi Phile,

I have just this kind of issue where I want to split my main table (Tbl_Claims) and archive the old historical records in a seperate table (Tbl_archive), where I can retrieve it easily if necessary.
Could you give me some directions how I can do that?
I have table with following key columns: received date and closed/open claims.
Records with receive date <#01-01-2003# and "closed" claims should be moved to Tbl_archive, or better ... if the records are older than 1 year and closed, move to Tbl_archive automatically.
I rather do this in the SQL Server than in my Access front-end, could you help me? Thanks in advance. John

 

[tils1975]

Hi ckleung

I have the same issue, did you get it resolved, if so please can you let me know how you got on.

regards

tils1975