How to archive records with records in related table(s)?

[David Portwood]

I want to archive records and related records during a user-specified pay
period (two weeks). In the main table is a field for PayPeriod, but in
related tables this field does not exist. How can move records in related
tables?

I'm thinking in terms of append/delete queries for this. Of course I could
write code to loop through each table, but that seems very inefficient.

 

[Amy Blankenship]

I want to archive records and related records during a user-specified pay
period (two weeks). In the main table is a field for PayPeriod, but in
related tables this field does not exist. How can move records in related
tables?

I'm thinking in terms of append/delete queries for this. Of course I could
write code to loop through each table, but that seems very inefficient.

If the table is related to one with the information, just use a query that
joins the two. Why are you moving the records? Why not just add a flag
"isArchived"?

-Amy

 

[John W. Vinson]

I want to archive records and related records during a user-specified pay
period (two weeks). In the main table is a field for PayPeriod, but in
related tables this field does not exist. How can move records in related
tables?

I'm thinking in terms of append/delete queries for this. Of course I could
write code to loop through each table, but that seems very inefficient.

Why the archive? Unless you're handling the payroll for General Motors or
larger, a full year's payroll records won't even make Access break a sweat.

John W. Vinson [MVP]

 

[David Portwood]

Thanks John and Amy, and anyone else who responds.

I have an app where 30 to 40 people are simultaneously browsing data. One of
the most valuable features is Access' built-in filtering capabilities, which
allows the users to restrict and group the data in various ways for their
individual purposes. There are also many reports from which the users can
select which usually involve totals calculations across related tables.

It seems like the more records that get entered, the longer it takes to set
a filter on the main form or generate a report. When there were just a few
thousand records, applying a filter seemed almost instantaneous. Now, with
about 12,000 records, the user typically waits maybe four or five seconds -
longer sometimes - to set a filter. Possibly this is caused by increased
network traffic or perhaps by conflicts with other users simultaneously
running reports? Wish I knew.

Can you tell me that it shouldn't take significantly longer to filter a
table (actually a query) with many records as a table/query with fewer
records? If so, I'll look elsewhere for the source of these delays.

My data becomes unneeded after a record has been "completed". And no, there
should be no need to recover completed records at a later date, but
nevertheless I do plan to add a "Restore Archived Data" feature - just in
case.

What I was hoping to hear is that I could create one append query and one
delete query, each of which would include all related tables, and
append/delete all records from all tables in one "swell foop". If this is
not possible, I will look at successively querying each related table
individually.

Can you (or anyone) clarify my understanding in these matters? Should I
expect longer delays setting a filter or generating a report as we add more
and more records? If so, wouldn't periodically "pruning" the table/query
improve this situation?

 

[Pieter Wijnen]

Mostly due to lack of or "wrong" indexes.
Many are not aware of the fact that if you have a multi-field index & you
don't filter on the first field of the index, the index won't be used at
all.
(Full Table scan)
Also often the Queries are not optimized for performance

HtH

Pieter

 

[David Portwood]

The filter is selected by the user "Filter By Selection" or "Filter By
Form". The user might choose any field or any combination of fields to set a
filter. Should I create an index for every field? That seems like a lot of
overhead. However, I'll go back and make sure that the most obvious filter
fields are indexed.

I remember reading about query optimization in my Access textbook but I
didn't specifically include anything like "Rushmore" optimization in my
code. I'll go back and have another look at ideas like that.

Even if I can get the queries running faster with optimization, will they
run fast when there are a million records in the database? At some point I
must begin archiving, right? Or no?

"Pieter Wijnen"

 

[Pieter Wijnen]

Not as such, you should however Compact Regulary to avoid internal
fragmentation.
I trust you have split the Db?
In time it might be advisable to upgrade the backend to a "proper" RDBMS
It is also an idea to have an initial filter for the data (WhereCondition),
on an index
to reduce the amount of data pulled into memory
And yes, you usually don't want to index all fields.
Designing the Queries SQL to help the optimizer is a good idea
avoid using "Not In" subselects if possible & know that Access has a general
flaw with IN clauses Uberhaupt

ie
SELECT ClientID FROM Client C
WHERE ClientID IN (SELECT ClientID From Appointment A
WHERE AppDate = Date())

Does not Create a Join, You have toWrite

SELECT ClientID FROM Client C
WHERE ClientID IN (SELECT ClientID From Appointment A
WHERE AppDate = Date() AND A.ClientID=C.ClientID)

Which is better written as

SELECT ClientID FROM Client C
WHERE Exists (SELECT 'X' From Appointment A
WHERE AppDate = Date() AND A.ClientID=C.ClientID)

IMnSHO anyway


HtH

Pieter

 

[Amy Blankenship]

Thanks John and Amy, and anyone else who responds.

I have an app where 30 to 40 people are simultaneously browsing data. One
of the most valuable features is Access' built-in filtering capabilities,
which allows the users to restrict and group the data in various ways for
their individual purposes. There are also many reports from which the
users can select which usually involve totals calculations across related
tables.

It seems like the more records that get entered, the longer it takes to
set a filter on the main form or generate a report. When there were just a
few thousand records, applying a filter seemed almost instantaneous. Now,
with about 12,000 records, the user typically waits maybe four or five
seconds - longer sometimes - to set a filter. Possibly this is caused by
increased network traffic or perhaps by conflicts with other users
simultaneously running reports? Wish I knew.

Can you tell me that it shouldn't take significantly longer to filter a
table (actually a query) with many records as a table/query with fewer
records? If so, I'll look elsewhere for the source of these delays.

My data becomes unneeded after a record has been "completed". And no,
there should be no need to recover completed records at a later date, but
nevertheless I do plan to add a "Restore Archived Data" feature - just in
case.

If the queries you use to populate your forms and reports use
Completed=False in the WHERE clause, you shouldn't be filtering quite so
much data.

HTH;

Amy

 

[Paul Shapiro]

I've implemented archiving like you're discussing. I agree with the other
posters that it's worth trying to improve your existing query performance
before implementing the archiving functionality. Maybe for the common
filters you could include a drop down filter list, and you can make sure the
filter expression is optimizable? And as already suggested, you should have
the front end forms and code split in a separate mdb from the back end data.
Make sure each user has their own local copy of the front end db.

If you're archiving you need to individually add rows to the archive tables
and remove them from the live data tables. It can be messier than it seems,
because it's rare that you have just the data that you want to remove from
the main database tables. If you need some category-type tables (payGrade,
payType, etc.), then you have to decide how to handle changes in those
tables.

I ended up with several archive-type possibilities and keep a system table
indicating how each existing data table should be handled: a) Do not archive
b) Archive selected rows and delete from the original table c) Archive
selected rows and do NOT delete from the live data d) Copy all rows to the
archive without any deletions from the live data.

The system works, but it's a bit fragile. I use a transaction while
performing the archive so any errors can allow a complete rollback to a
stable state. If users have access to the archived data, they need to
understand what is archived and what it means. Reporting sometimes needs to
include archived data, which is more complicated than I would like.
Paul Shapiro

 

[John W. Vinson]

Can you tell me that it shouldn't take significantly longer to filter a
table (actually a query) with many records as a table/query with fewer
records? If so, I'll look elsewhere for the source of these delays.

The query should be quick if you have Indexes on all of the fields used for
searching or sorting the records. Open the table in design view and see if
these fields are indexed. If they're not, yes, you will see a dramatic
improvement when you index them.

My data becomes unneeded after a record has been "completed". And no, there
should be no need to recover completed records at a later date, but
nevertheless I do plan to add a "Restore Archived Data" feature - just in
case.

Ummm... so you'll never need to do month-to-date or year-to-date calculations,
annual reports, summaries, tax reports? Sounds really odd for a payroll system
if that's what it is!

What I was hoping to hear is that I could create one append query and one
delete query, each of which would include all related tables, and
append/delete all records from all tables in one "swell foop". If this is
not possible, I will look at successively querying each related table
individually.

You can indeed create such queries, and execute them in one swell foop from a
macro or (better) from visual basic code. It's an added layer of complication
(real tables and archive tables, the hassle of keeping them in synch, being
sure the archive queries get run, etc.) - enough so that I'd really recommend
trying the indexing. If performance is unacceptable even with the indexing,
then it may be worth the extra effort - but try the simple solution first!

John W. Vinson [MVP]

 

[John W. Vinson]

If the queries you use to populate your forms and reports use
Completed=False in the WHERE clause, you shouldn't be filtering quite so
much data.

.... just provided you have a nonunique Index on the Completed field. Some
folks advise never indexing yes/no fields, but if you'll typically be
selecting the smaller subset of the data, it can make a dramatic difference.

John W. Vinson [MVP]

 

[Pieter Wijnen]

Esppecially true for Oracle Bitmap Indexes

Pieter

 

[David Portwood]

I'm a little embarrassed to admit I found my main table had several popular
"filter" fields which were not indexed. After indexing these fields,
performance did indeed improve dramatically.

My app is not a payroll system. It is a management tool which tracks
in-house work from desktop to desktop through our department. We add
approximately 500 records to the db each day (relating to yellow page ads,
if anyone's interested) and that number is growing. I should think that at
some point an archival process might become necessary, but it looks like I
won't have to worry about that any time soon, thanks to help from this
group.

Much obliged to all. I hope I can reciprocate, sometime.

 

[John W. Vinson]

I'm a little embarrassed to admit I found my main table had several popular
"filter" fields which were not indexed. After indexing these fields,
performance did indeed improve dramatically.

Don't feel alone... I fixed a similar problem in a system which had been in
production for five years. The users were (at first) confused because the
accustomed several-second delay went to basically instant response - they
didn't realize the query had even run!

My app is not a payroll system. It is a management tool which tracks
in-house work from desktop to desktop through our department. We add
approximately 500 records to the db each day (relating to yellow page ads,
if anyone's interested) and that number is growing. I should think that at
some point an archival process might become necessary, but it looks like I
won't have to worry about that any time soon, thanks to help from this
group.

Ok... 500/day x 240 workdays/yr, 120,000 records a year; after ten years
you'll be up to 1.2 million. Even THAT is not hard for Access to handle.

John W. Vinson [MVP]

 

[Pieter Wijnen]

I once tookover a report that took 6+ hours to run, after some redesign I
brought it down to 15 secconds.
Needless to say it's the worst designed table structure I've ever seen

Pieter