To archive, or not?

[a24t42]

I have reading some of the back posts about archiving records. I get
the feeling that the consensus is not to do it. My question is - when
is it appropriate, if ever, to archive records?

With the database I am working with it has about 140,000 records. The
table Production only uses about 3,000 of those records. It is slow to
open. I can only think that things would speed up if I archived some of
the old records. Your thoughts, please?

Thanks in advance
24t42

 

[a24t42]

Jerry,

Thank you for your input. What you say makes sense. I am somewhat new
to Access so my understanding of the ins and outs is limited. I was
asked to some changes to the database, which I did not design.

My database is for a repair facilty so for the main tables I have
Customers (contains basic information), Pickup (contains infomation
about the repair job), and Cards (one card for each piece of equipment.
One pickup can have multiple cards). My form Production is based on a
query that sorts for records that have a null invoice date and were
received after 1/1/2005. It works off the card number. This is a select
query and not a union query. It seems sluggish in opening. I don't know
if it makes a differnece or not in the speed of opening but the data
files are linked to the tables and the database is on a company server
as several people use it.

Thanks again for your valuable input.

24t42

 

[a24t42]

I will try that tomorrow. I ran the analyzer today to see what it would
say. It suggested 2 things -

1. It suugested that I convert the macros to modules. Now I know very
little about VB but I can write a macro. Do modules really run any
faster macros?

2. It suggested in several places that I include an Option Exlicit
statement. I have read other posts here talking about the need to do
this so I know I need to do this. My question is - will one statement
do for the whole database or do I need one for each module?

Thanks in advance.
24t42

 

[Office User]

A question for all the experts out there related to archiving or not to a
separate table. I've read and agree with the reasons for not archiving when
it involves moving data to a new table. However, I'm trying to find the best
way not to archive my employee data in the following situation:

I have 1 table containing Employee name, computer username, ID#, job title
and area of responsibility (coded for sales territories). Right now my
computer username or ID can be the primary key if I use a true normalized
table structure. I need to be able to track active vs inactive employees so
have added a yes/no field for Active and Effective Date to know when change
took place. My real dilemna comes that employees may change positions which
means a title change so they'd be in my table twice (can't happen if using
primary key) and we've had one or two leave so the Active field is changed to
No. But then they come come back after a couple months so I would end up
with 2 records for same person but 2 different dates (again not proper for
true normalized data).

So - is there a way to keep this truely normalized, keep historical data and
NOT archive it to a different table?

Thanks for your help,
Marcia

 

[Jerry Whittle]

Great question.

I'd handle it with another table called something like Position that is
linked to your first table. In it you could track the position, start date,
and end date for the position. You could leave Active in the main table or
use some logic in the Position table like if the max end date is larger than
the max start date, they must have left the company.