How to approach archiving records design....

[Damon Heron]

As my database is used, the tables get filled with products that are no
longer available, orders that are one-time only, customers that are
obsolete, etc. In general, how does one handle archiving all of these old
records while still maintaining db integrity? As an example, I have a
products form, with an inventory transaction subform, that has 175 different
products, of which only 20-25 are active. I put a "discontinued" checkbox
on the form, so the user can filter for active products, but this seems sort
of kludgy. Any and all ideas are appreciated.

Damon

 

[Jeff Boyce]

Damon

Not sure I'd agree that a "discontinued" check/field is a kludge. If you
were to "archive" (place somewhere else), all your "old" customer purchase
records would be orphaned! A personal opinion, but I am very much against
setting up "archives" when you can use a Yes/No field, or a DateTime field
to store what you need to use to remove certain records from daily use.

If the issue is that your users are seeing too many records that they don't
need to, another way to cast your situation might be: "how to I remove FROM
VIEW records I no longer need to see in every day operations?"

Good luck!

Jeff Boyce
<Access MVP>

 

[Damon Heron]

Thanks, Jeff. I hadn't thought about a date parameter (don't show records
prior to a certain date) - but I guess I would have to have a method for the
user to override the parameter without too much trouble, in case there was a
need to look at an old transaction.

Damon

 

[Jeff Boyce]

Damon

An approach that's worked for me is to assume that only "active" rows should
show, and build a query accordingly.

Then add a way (a check box works) to let the user say "Show me everything".

In the AfterUpdate event, replace the "source" with a query that does NOT
limit rows to those that are active.