Tables

[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!

 

[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

 

[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...

 

[Graham R Seach]

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

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

 

[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>

 

[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.

 

[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

automatically "archive" all the records

 

[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>

 

[Graham R Seach]

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

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

 

[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.