Archiving Records

[Graham]

Is there a preferred method for archiving redundant client records, without
actually deleting them, so that they are automatically excluded from
Queries/Reports etc, but the data remains on the main table & accessible for
future reference, if ever required.
Many Thanks

 

[Allen Browne]

Just add a yes/no field named (say) Inactive.
Set it to Yes when the client is no longer active.

Create a query such as:
SELECT * FROM tblClient WHERE Inactive = False;
Use this anywhere you need a list of clients.

 

[Jeff Boyce]

Allen's suggestion will give you a way to mark any record as "Inactive" and
allow you to exclude it via Query.

Your use of the word "redundant" could be interpreted to mean "duplicate".
Or are you saying you have "historical" client records you no longer wish to
view (but do wish to keep)?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Al Campagna]

Graham,
That's really not what is normally considered to be "archiving."
Say you had a table of records that span from 2000 to 2008, and you want
to archive any record before, and up to, 2006.
You would develop 2 queries.
One... query to Update another table (ex. tblMyArchive) with all
records 2006 or less.
Two... a Delete query to remove them from the original table.
That's really what archiving involves.

But, what you requested... is much simpler.
Given the same table setup as above, you just need to criteria the query
that is the RecordSource for any form, query, or report.
For example, on a form, the query behind that form should have a
criteria against your date field like this...
YourDateField

#12/31/06#

Now that will work, but requires that every year you move up the
criteria's cutoff date.
A better method is to always ask for records older than say...2 years,
before today's date.
YourDateField

DateAdd("yyyy",-2,Date())

This will only show dates greater than two year's before today's date,
and never needs to be changed. And, leaves all the records in the table.

Note: as years go by (if you add MANY records each year)...your query
method might show some performance issues. Each query behind each
report.query or form has to run against ALL records. So, you may
eventually want to use the real "Archiving" method.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Graham]

Thanks to each of you for your replies.
I have already done what Allen suggested which works fine, however, I'm
handing this database over to others and I was wondering whether this Field
(which I've called "Archive"), when ticked "Yes", can be set by default to be
excluded from queries/reports etc. If others use it and forget to include the
'Omit Flagged Records' then they may send out info to persons who we may not
want to be contacted.
If new appplications come in, I want to keep the old records so that we
don't add people back onto the list, that we may have had dealings with in
the past, and would prefer NOT to deal with again.

Jeff, Yes, I have historical client records of people that I don't want to
appear inadvertently on queries etc. but which I do wan tto save for the
reason given in the previous paragraph.

Al, Could your suggestion of a Query to move tagged records (Redundant
records) to a separate table be made to work automatically, i.e. immediately
the record was ticked ? This presumably would prevent records showing up by
default in queries etc., but they would still be available to reference to
when required ?
Dates as such would not necessarily be relevant in this case.

Regards
Graham

 

[MASSIMO]

Thanks to each of you for your replies.
I have already done what Allen suggested which works fine, however, I'm
handing this database over to others and I was wondering whether this
Field
(which I've called "Archive"), when ticked "Yes", can be set by default to
be
excluded from queries/reports etc. If others use it and forget to include
the
'Omit Flagged Records' then they may send out info to persons who we may
not
want to be contacted.
If new appplications come in, I want to keep the old records so that we
don't add people back onto the list, that we may have had dealings with in
the past, and would prefer NOT to deal with again.

Jeff, Yes, I have historical client records of people that I don't want to
appear inadvertently on queries etc. but which I do wan tto save for the
reason given in the previous paragraph.

Al, Could your suggestion of a Query to move tagged records (Redundant
records) to a separate table be made to work automatically, i.e.
immediately
the record was ticked ? This presumably would prevent records showing up
by
default in queries etc., but they would still be available to reference to
when required ?
Dates as such would not necessarily be relevant in this case.

Regards
Graham

 

[Al Campagna]

Graham,

Al, Could your suggestion of a Query to move tagged records
(Redundant records) to a separate table be made to work
automatically, i.e. immediately the record was ticked ?

Yes... you'll need an Update query. Use the OnDelete event of the form.
That runs the Update query... just before the Delete, according to some key
unique value on that particular record. Like CustID or EmpID... etc.

Try the Active/Inactive suggestion/s first, and leave your old recs in
the same table. That's the easiest way to go, as you originally wanted to
keep the records within the same table... Then, if you see performance
issues with the filtering, you could try a real "archive" to another table.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Stockwell43]

Hi Al,

What you suggested to Graham is what I am looking for only for 3 months at a
time. Do you think you could tell me what I need and how to set it up? The
help would be greatly appreciated.

Thanks!!

 

[Jeff Boyce]

Pardon my intrusion...

If you open a query in design view, add the date/time field for output, and
use something like the following (untested):

Date()-90

as a selection criterion for that field, you'd get all records with a
date/time value more recent than three months old.

If "90" doesn't work quite exactly enough, take a look at using the
DateDiff() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP