archiving old data 2

[jimdevon]

Help, I have a customers table with a related services table. I want to remove older customer records by using a make table query (arccustomers), and then do the same with the services table, but still maintain (or restore) the relationship. I have tried this method but cannot restore the relationship. Any suggestions will be appreciated.
Thanks jim.

 

[HFloyd]

Dear Jim,

I assume you would like to run this process more than once (in order to continue archiving records?)

If this is the case, what I would reccommend would be to Manually create the archive tables and the run append queries to add the records.

Also, am I correct in assuming that you are using unique record numbers created with AutoNumber fields to link the customers to the services?

Heather

http://www.floydinnovations.com

 

[jimdevon]

Thanks for the response Heather. Yes you are correct on all counts. I did use autonumber to creat unique records and used this autonumber field to link the two tables. The problem is that the archived customer records no longer are linked (related) to the services once they are moved. I have worked around this by creating two new tables (customer and services) and creating a new query relating the records again. So, I guess it is fixed. Thanks again for responding.

 

[JeremyNYC]

Jim,

How much data do you have? If it's not massive, I would just add a date field to indicate the date it was declared archived. Then your queries could just pull all records that are null in that field. When you want to see the old stuff you can include all records or include records that are null or have a value less than some date you choose.

If it is massive, probably the easiest way to do it is to create another database and have exactly the same table in that other table. Then you could have a routine that switches from pointing to one back end file to pointing to the other (for that table).

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[HFloyd]

Dear Jim,

If you have solved your problem that's great, otherwise, the way my previous suggestion would work would be if you create the two "archive" tables with the ID field marked as "Number" - not autonumber and relate them in the Relationships window.

When you import the data, it should maintain the same ID numbers. The only problem I could see is if you compact the database and it begins auto numbering in your original table to cover the "archived" records. I think you can set an option somehwere for that though...

Heather

http://www.floydinnovations.com

 

[evalesthy]

There is a great discussion of this topic that JeremyNYC alluded to above. See thread 702-455987 in the Access Forms forum. It may be more involved than you want to get - but certainly worth a look.

 

[jimdevon]

thanks all.