Tables

G

Greg

Hi all,
After a customer account is closed, I would like to move
the customer records to a new database (to be used only
for closed accounts and accounts inactive for 2 years),
but I should still be able to retrieve the records from
my current database.

Can anyone please help!
 
G

Graham R Seach

Greg,

<<...move the customer records to a new database...>>
Don't! Add a Boolean (Yes/No) column to the customer table, called
"IsActive". Set its DefaultValue property to True (-1). At the appropriate
time, set that field to False (0). Then you need to adjust all your queries
to return only those records whose IsActive field=True. This is the
preferred way of doing things.

You can create an AutoExec macro to automatically "archive" all the records
that closed or have been inactive for >=2 years.

If you tell us what the relevant tables and fields are, we can help you
write the UPDATE query for it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
J

Jeff Boyce

To build on Graham's response, you could decide that you need to know WHEN
the account was closed. In this situation, use a date/time field and record
the DateClosed. Modify your queries/reports accordingly. You still have
all the data in your table, and don't have any particular problems doing
historical/longitudinal reporting...
 
G

Graham R Seach

That's a far better idea Jeff. Much more useable.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
J

Jeff Boyce

Only to you and me, Graham! I would set up a system to keep that info, but
few other folks need it or care... <g>

Jeff Boyce
<Access MVP>
 
P

PC Datasheet

Make an archive table that is a duplicate of your customers table. You can
then build separate forms for retrieving data from this table or you can use
your existing forms by adding code to change the recordsource of your forms
to the archive table. Also, if you need to simultaneously retrieve records
from your active table and your archive table, you can use an union query.
 
G

Greg

thanks for your help.
the relevant table and fields are
CustomerTbl: CustomerAccNo--Text field
AccountStatus--Text field
DateClosed---Date/Time field

-----Original Message-----
Greg,

<<...move the customer records to a new database...>>
Don't! Add a Boolean (Yes/No) column to the customer table, called
"IsActive". Set its DefaultValue property to True (-1). At the appropriate
time, set that field to False (0). Then you need to adjust all your queries
to return only those records whose IsActive field=True. This is the
preferred way of doing things.

You can create an AutoExec macro to
Click to expand...
automatically "archive" all the records
 
J

Jeff Boyce

Not sure what you would gain by having two tables with duplicate structure.
It certainly sounds like more work to keep the separate tables coordinated
(via UNION query), and records moved from one to the other, than to simply
"mark" a record as archived in a single table, but that's just one person's
opinion...

Jeff Boyce
<Access MVP>
 
G

Graham R Seach

<<but that's just one person's opinion...>>
Uhm...Two. :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
L

Larry Daugherty

If we're voting, make it 3.

IMHO the only valid reasons for "archiving" are 1) performance. and 2)
approaching the 2G size limit. I've never experienced 2). As for the
archiving itself, I just rename a copy of the back end to something creative
like AppXXXended050215.mdb and then run a delete query to purge every record
before a given date from the active system.
 
Top