-----Original Message-----
Dear Ron:
Please allow me to provoke some thinking.
By "purge accounts" I'm thinking you are expecting to simply discard
all the information for these accounts from your database. This would
typically include information in several tables: orders and order
details, records of hours billed, payments made, shipments made,
inventory shipped, etc.
Of course, if the hours billed is also a record of what each employee
worked, or if the inventory shipped is also part of a system to track
inventory or sales, you could not do so without destroying those same
records from the perspective of what work each employee did, or what
sales you had of each inventory item. Even if such systems have not
yet been created, you may want this information for future reference.
In fact, are you that certain that no one will ever want to see any of
the information on those accounts again?
Perhaps your hard drive is almost full, and it won't hold more
records. Or perhaps your database in nearing the 2 GB limit for Jet
or MSDE databases. Unless this is the case, why not keep the
information for a while? And, even if this is the case, there are
ways to accomodate this anyway.
Now, here's my first suggestion. You can make those accounts
"disappear" from most of your applications without removing all the
information you have stored. Simply use the "rule" you have which
allows you to decide which accounts are to be purged to limit which
accounts are displayed on the accounts form, and in combo boxes where
you select accounts. However, leave a check box on those forms to
disable the "purgeing" so that a user can reference those accounts
again.
Then, if an account "reactivates" you need only check this check box
and begin re-using that account. You won't have to re- type all the
information about that account, and all that account's history will
instantly re-appear when they are re-activated by simply posting new
transactions for that account.
There are many variations possible. You might want to put a field in
the account table that says which accounts are active, and simply
change that to "not active" when you want to purge accounts.
Now, finally, here's a way to accomplish either the above suggestion,
or to actually delete things the way you are probably thinking.
Create a purge table, and put in it one or more columns that match the
primary key of your current account table. Use the totals query you
have to put these primary key values into this purge table by changing
your totals query into an append query. Then you can use the purge
table against all the existing tables containing account information
to "purge" them in whatever method you choose.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
We purge accounts that haven't had activity in the last
year.
I use a Totals query to pull the max [Activity Date] per
account. I then use an expression that calculates the
Purge date and only displays those with Purge dates less
than Date().
The problem is that I can't click the Purge box because
my data is going through a Totals query (and it thereby
locks the records).
Anyone happen to know the easiest way around this?
End result, is I only want to see the accounts needing to
be purged.
Thanks,
Ron
.
