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

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



Jerry Whittle wrote:
> I'm against archiving data. You say that the Production table only has 3,000
> of the 140,000 records in the database. What are the other tables in the DB
> doing? If you have Union queries that link the Production table to other
> similar tables, that's the problem.
>
> IMHO the best way to archive data is to put a Yes/No field in the original
> table named something like Active. When you don't want to see that record
> again, uncheck it. Run all of your queries where you only want to see active
> records with the criteria of Yes (no quotation marks) against the Active
> field.
>
> Reasons?
> (1) You don't have to worry about moving records between tables.
> (2) You don't have to worry about duplicates either in the Archive table or
> between the two tables.
> (3) If you ever need to search for both active and inactive records, you
> don't need an inefficient union query.
>
> About the only reason to archive data is because the database file is
> getting near the max size limitation. If that happens you'll need to move the
> data to another .mdb file anyway. Might be time to look into another RDBMS
> which can hold more data.
>
> One more thing about archived data and I'll get off my soapbox: Archived
> data is lost data. Somewhere along the line someone will make a change to the
> database which will prevent moving the archived data back into the table
> without a lot of work. Seen this way too often. My worse case was data saved
> to a tape backup system. When needed we no longer had that kind of tape
> backup machine! Once we found on, many of the tapes has unrecoverable errors.
> So much for archiving data.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "(E-Mail Removed)" wrote:
>
> > 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

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


Jerry Whittle wrote:
> You could try indexing the invoice date field; however, indexes don't work
> with nulls. What I've done in this case is make the default value for such a
> field something bogus like #1/1/1950#. Then instead of nulls you look for
> #1/1/1950#. With an index it will find those records much, much faster.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "(E-Mail Removed)" wrote:
>
> > 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
> >
> >
> >
> > Jerry Whittle wrote:
> > > I'm against archiving data. You say that the Production table only has 3,000
> > > of the 140,000 records in the database. What are the other tables in the DB
> > > doing? If you have Union queries that link the Production table to other
> > > similar tables, that's the problem.
> > >
> > > IMHO the best way to archive data is to put a Yes/No field in the original
> > > table named something like Active. When you don't want to see that record
> > > again, uncheck it. Run all of your queries where you only want to see active
> > > records with the criteria of Yes (no quotation marks) against the Active
> > > field.
> > >
> > > Reasons?
> > > (1) You don't have to worry about moving records between tables.
> > > (2) You don't have to worry about duplicates either in the Archive table or
> > > between the two tables.
> > > (3) If you ever need to search for both active and inactive records, you
> > > don't need an inefficient union query.
> > >
> > > About the only reason to archive data is because the database file is
> > > getting near the max size limitation. If that happens you'll need to move the
> > > data to another .mdb file anyway. Might be time to look into another RDBMS
> > > which can hold more data.
> > >
> > > One more thing about archived data and I'll get off my soapbox: Archived
> > > data is lost data. Somewhere along the line someone will make a change to the
> > > database which will prevent moving the archived data back into the table
> > > without a lot of work. Seen this way too often. My worse case was data saved
> > > to a tape backup system. When needed we no longer had that kind of tape
> > > backup machine! Once we found on, many of the tapes has unrecoverable errors.
> > > So much for archiving data.
> > > --
> > > Jerry Whittle, Microsoft Access MVP
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > >
> > >
> > > "(E-Mail Removed)" wrote:
> > >
> > > > 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
> >
> >

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" wrote:

> 1. Modules are faster at running code plus they have much better error
> handling. However even a module won't make your query run faster; it might
> call on the query to run sooner at best. I doubt that it would make things
> run more than a second faster.
>
> 2. Option Exlicit should be at the top of each module.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
> "(E-Mail Removed)" wrote:
>
> > 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
> >
> >
> > Jerry Whittle wrote:
> > > You could try indexing the invoice date field; however, indexes don't work
> > > with nulls. What I've done in this case is make the default value for such a
> > > field something bogus like #1/1/1950#. Then instead of nulls you look for
> > > #1/1/1950#. With an index it will find those records much, much faster.
> > > --
> > > Jerry Whittle, Microsoft Access MVP
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > >
> > >
> > > "(E-Mail Removed)" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > >
> > > > Jerry Whittle wrote:
> > > > > I'm against archiving data. You say that the Production table only has 3,000
> > > > > of the 140,000 records in the database. What are the other tables in the DB
> > > > > doing? If you have Union queries that link the Production table to other
> > > > > similar tables, that's the problem.
> > > > >
> > > > > IMHO the best way to archive data is to put a Yes/No field in the original
> > > > > table named something like Active. When you don't want to see that record
> > > > > again, uncheck it. Run all of your queries where you only want to see active
> > > > > records with the criteria of Yes (no quotation marks) against the Active
> > > > > field.
> > > > >
> > > > > Reasons?
> > > > > (1) You don't have to worry about moving records between tables.
> > > > > (2) You don't have to worry about duplicates either in the Archive table or
> > > > > between the two tables.
> > > > > (3) If you ever need to search for both active and inactive records, you
> > > > > don't need an inefficient union query.
> > > > >
> > > > > About the only reason to archive data is because the database file is
> > > > > getting near the max size limitation. If that happens you'll need to move the
> > > > > data to another .mdb file anyway. Might be time to look into another RDBMS
> > > > > which can hold more data.
> > > > >
> > > > > One more thing about archived data and I'll get off my soapbox: Archived
> > > > > data is lost data. Somewhere along the line someone will make a change to the
> > > > > database which will prevent moving the archived data back into the table
> > > > > without a lot of work. Seen this way too often. My worse case was data saved
> > > > > to a tape backup system. When needed we no longer had that kind of tape
> > > > > backup machine! Once we found on, many of the tapes has unrecoverable errors.
> > > > > So much for archiving data.
> > > > > --
> > > > > Jerry Whittle, Microsoft Access MVP
> > > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > > >
> > > > >
> > > > > "(E-Mail Removed)" wrote:
> > > > >
> > > > > > 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
> > > >
> > > >
> >
> >

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Office User" wrote:

> 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" wrote:
>
> > 1. Modules are faster at running code plus they have much better error
> > handling. However even a module won't make your query run faster; it might
> > call on the query to run sooner at best. I doubt that it would make things
> > run more than a second faster.
> >
> > 2. Option Exlicit should be at the top of each module.
> > --
> > Jerry Whittle, Microsoft Access MVP
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> > "(E-Mail Removed)" wrote:
> >
> > > 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
> > >
> > >
> > > Jerry Whittle wrote:
> > > > You could try indexing the invoice date field; however, indexes don't work
> > > > with nulls. What I've done in this case is make the default value for such a
> > > > field something bogus like #1/1/1950#. Then instead of nulls you look for
> > > > #1/1/1950#. With an index it will find those records much, much faster.
> > > > --
> > > > Jerry Whittle, Microsoft Access MVP
> > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > >
> > > >
> > > > "(E-Mail Removed)" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > > > Jerry Whittle wrote:
> > > > > > I'm against archiving data. You say that the Production table only has 3,000
> > > > > > of the 140,000 records in the database. What are the other tables in the DB
> > > > > > doing? If you have Union queries that link the Production table to other
> > > > > > similar tables, that's the problem.
> > > > > >
> > > > > > IMHO the best way to archive data is to put a Yes/No field in the original
> > > > > > table named something like Active. When you don't want to see that record
> > > > > > again, uncheck it. Run all of your queries where you only want to see active
> > > > > > records with the criteria of Yes (no quotation marks) against the Active
> > > > > > field.
> > > > > >
> > > > > > Reasons?
> > > > > > (1) You don't have to worry about moving records between tables.
> > > > > > (2) You don't have to worry about duplicates either in the Archive table or
> > > > > > between the two tables.
> > > > > > (3) If you ever need to search for both active and inactive records, you
> > > > > > don't need an inefficient union query.
> > > > > >
> > > > > > About the only reason to archive data is because the database file is
> > > > > > getting near the max size limitation. If that happens you'll need to move the
> > > > > > data to another .mdb file anyway. Might be time to look into another RDBMS
> > > > > > which can hold more data.
> > > > > >
> > > > > > One more thing about archived data and I'll get off my soapbox: Archived
> > > > > > data is lost data. Somewhere along the line someone will make a change to the
> > > > > > database which will prevent moving the archived data back into the table
> > > > > > without a lot of work. Seen this way too often. My worse case was data saved
> > > > > > to a tape backup system. When needed we no longer had that kind of tape
> > > > > > backup machine! Once we found on, many of the tapes has unrecoverable errors.
> > > > > > So much for archiving data.
> > > > > > --
> > > > > > Jerry Whittle, Microsoft Access MVP
> > > > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > > > >
> > > > > >
> > > > > > "(E-Mail Removed)" wrote:
> > > > > >
> > > > > > > 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
> > > > >
> > > > >
> > >
> > >