What is the best approach to archive records (or handle a done status)

[Pack10]

What is the best approach to archiving records in the database.
In my case, when the user is done with the job, they put a status of ARCHIVE on the record.
Is it best to keep the ARCHIVE record in the regular transaction (or Assignments) table with the ARCHIVE status.....or is it a good idea to write the archive record to a separate table.

 

[dhookom]

I typically set a status field. There have been only about 2% of my solutions where I have moved the records to an archive database and it had to do with the size of the tables and/or someone else determined the spec.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I would say - it depends.
If you have a few million (big/long) records and most of them are Archived, and fields are NOT indexed in your table, I would say: move them to another table.
If that's not the case, have a Status field and keep them in the same table.

In my tables I have a Status fiel that holds only one letter. My Archive records have Z in them. I have another small table:[tt]
Status Desc
N New
A Active
H On-Hold
D Deleted
....
Z Archived[/tt]

Have fun.

---- Andy

 

[papadba]

How often are the archived entries used?

Do they have any performance impact being combined with the non-archived entries?

Is the same user population permitted to see all of the entries or are there some users who must work with the "active" data only (or the archived data only)?

 

[TheAceMan1]

How are ya Pack10 . . .

I'm in total agreement with [blue]dhookom[/blue] ... particularly if your primarykeys are not autonumbers (as they shouldn't be if the db is on a network). There's a chance of overwriting a primarykey that was archived out. Then when you attempt to bring that record back in you'll get a [blue]Duplicate PrimaryKey[/blue] error!

This may not be the case for you, but it should be considered ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[abulaila11]

particularly if your primarykeys are not autonumbers (as they shouldn't be if the db is on a network)!! Could any one tell me in details Why??

 

[hmlhml]

yes, as a noobie I'd like to know that one too. How do you prevent errors from duplicate primary keys if you don't use autonumbers? Or do you trap the error and have another go?

 

[TheAceMan1]

abulaila11, hmlhml . . .

So sorry ... should read:

[blue] ... particularly if your primarykeys are autonumbers ...[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[abulaila11]

Thanks! TheAceMan1

 

[hmlhml]

thanks... actually on a related issue (that I don't feel is important enough to start a new thread), what's the approved way to deal with the situation where records must be numbered sequentially without duplicates? All the literature on autonumbers stresses that although they are currently sequential, there is no promise that they will always remain so in future!
Any advice gratefully received.

 

[TheAceMan1]

hmlhml . . .

Why the dependency on [blue]sequential without duplicates?[/blue]

If your talking [blue]primarykeys[/blue], its more than sufficient to simply have a unique ID for each record ... sequential or not.

If its absolutely necessary, a seperate field (aside from the primarykey) can be used for the sequential numbers. This field is generated & maintained by you. The trickest part is what to do when a deletion involves a value within first & last.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hmlhml]

sorry Aceman, haven't been here much and missed your reply until today. I meant things like purchase order or invoice numbers, which people often want to see incrementing in a logical manner, but which also should never be duplicates. I get the impression most people use autonumbers.

 

[TheAceMan1]

hmlhml . . .

What people are use to is one thing ... what they need to get use to is another. [blue]Uniqueness[/blue] is one of these items. I've gone as far as to give classes on this ... just to make it easier for me! When you think about deletions and additions, its really hard to [blue]maintain sequential![/blue] The classes relieve me of this and brings the users closer to the proper view of things in a db.

[blue]I get the impression most people use autonumbers.[/blue]

Autonumbers have their place ... but [red]never on a network[/red] where more than one user will be accessing the db (multi-user). You should get in the habit of maintaining your own primarykeys, jut incase the db could wind up on a network. AutoNumbers can wreck havoc in a multi-user environment. Aside form multi-user, autonumbers are just fine.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]