Cleaning up database

I have about 10 years worth of data in a database, and i would like to only
have 2 years. Is there a way to strip out the other 8 years of data and put
it in a seperate database?

Why? Are you close to the size limits (1 Gb for Access 97 and earlier, 2 Gb
for Access 2000 and newer)?

If you're not near the size lmits, there's no reason to remove the data.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Cameron" wrote in message
...
I have about 10 years worth of data in a database, and i would like to only
have 2 years. Is there a way to strip out the other 8 years of data and
put
it in a seperate database?

If it's all in one table with no relationships to other tables, its fairly
simple.
First backup the existing database.
Then duplicate the table (definition only not data) in the existing database.
Then write 2 queries: one to copy over the relevant data to the new table
and a second one to delete the relevant data in the source table.
Then you can create a brand new database and import the table with the old
data and delete it from your old database.
-Dorian

"Cameron" wrote:

I have about 10 years worth of data in a database, and i would like to only
have 2 years. Is there a way to strip out the other 8 years of data and put
it in a seperate database?

One disadvantage to "moving" data like this, storing some records in one
place and other records in another table, is finding "old" records -- "now,
which table did I leave those in...?"

Another disadvantage is the processing (you REALLY need to get it right when
you start deleting tables/data).

Before undertaking a copy and delete approach, it bears considering just how
many records are involved, and what the user is hoping to accomplish. If
the user only wants to "hide" the old records from daily use, this is easily
done by adding a single field in the existing table and marking those
records that need to be hidden.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"mscertified" wrote in message
...
If it's all in one table with no relationships to other tables, its fairly
simple.
First backup the existing database.
Then duplicate the table (definition only not data) in the existing
database.
Then write 2 queries: one to copy over the relevant data to the new table
and a second one to delete the relevant data in the source table.
Then you can create a brand new database and import the table with the old
data and delete it from your old database.
-Dorian

"Cameron" wrote:

I have about 10 years worth of data in a database, and i would like to
only
have 2 years. Is there a way to strip out the other 8 years of data and
put
it in a seperate database?

If I understand the question correctly, the idea is to have just the past
two years, in which case basing a form or report on a query with DateAdd as
the criteria for a date field should work. Help has more information about
the DateAdd function. This suggestion assumes that file size is not
excessive, as mentioned in another response.

"Cameron" wrote in message
...
I have about 10 years worth of data in a database, and i would like to only
have 2 years. Is there a way to strip out the other 8 years of data and
put
it in a seperate database?

Well, the file size isn't excessive. And for the most part I think I could
just hide the data so that the user only saw the past two years.

In one of my past jobs I was asked about split off data that pertained to
specific years due to accounting purposes. The accountant wanted to
physically have the front end and only the data pertaining to that year to
see and to show the auditors. The senior programmer wrote a subroutine to
functionally build a new database and populate the tables with the prior
years data and then remove the prior years data from the current database.

So even though Access is capable of 2 gig of storage there can still be
situations where you would need to split out the data to satisfy a higher
power such as Revenue Canada.

"BruceM" wrote:

If I understand the question correctly, the idea is to have just the past
two years, in which case basing a form or report on a query with DateAdd as
the criteria for a date field should work. Help has more information about
the DateAdd function. This suggestion assumes that file size is not
excessive, as mentioned in another response.

"Cameron" wrote in message
...
I have about 10 years worth of data in a database, and i would like to only
have 2 years. Is there a way to strip out the other 8 years of data and
put
it in a seperate database?

that's why I said 'back up the existing database first'.

"Jeff Boyce" wrote:

One disadvantage to "moving" data like this, storing some records in one
place and other records in another table, is finding "old" records -- "now,
which table did I leave those in...?"

Another disadvantage is the processing (you REALLY need to get it right when
you start deleting tables/data).

Before undertaking a copy and delete approach, it bears considering just how
many records are involved, and what the user is hoping to accomplish. If
the user only wants to "hide" the old records from daily use, this is easily
done by adding a single field in the existing table and marking those
records that need to be hidden.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"mscertified" wrote in message
...
If it's all in one table with no relationships to other tables, its fairly
simple.
First backup the existing database.
Then duplicate the table (definition only not data) in the existing
database.
Then write 2 queries: one to copy over the relevant data to the new table
and a second one to delete the relevant data in the source table.
Then you can create a brand new database and import the table with the old
data and delete it from your old database.
-Dorian

"Cameron" wrote:

I have about 10 years worth of data in a database, and i would like to
only
have 2 years. Is there a way to strip out the other 8 years of data and
put
it in a seperate database?

Even if the data volume isn't 'excessive', you are still wasting resources
and adding to response time by processing these records every time you run a
query. You are also risking that if the database corrupts you will lose all
the records. In my opinion its much safer to offload the old unused records
to another table.

-Dorian

"Cameron" wrote:

Well, the file size isn't excessive. And for the most part I think I could
just hide the data so that the user only saw the past two years.

In one of my past jobs I was asked about split off data that pertained to
specific years due to accounting purposes. The accountant wanted to
physically have the front end and only the data pertaining to that year to
see and to show the auditors. The senior programmer wrote a subroutine to
functionally build a new database and populate the tables with the prior
years data and then remove the prior years data from the current database.

So even though Access is capable of 2 gig of storage there can still be
situations where you would need to split out the data to satisfy a higher
power such as Revenue Canada.

"BruceM" wrote:

If I understand the question correctly, the idea is to have just the past
two years, in which case basing a form or report on a query with DateAdd as
the criteria for a date field should work. Help has more information about
the DateAdd function. This suggestion assumes that file size is not
excessive, as mentioned in another response.

"Cameron" wrote in message
...
I have about 10 years worth of data in a database, and i would like to only
have 2 years. Is there a way to strip out the other 8 years of data and
put
it in a seperate database?

Safeguards against corruption include a properly designed database and
backing up regularly. If you split off the older records and the database
corrupts, the only "saved" records will be the old ones. I can't point to
specific benchmarks, but I have read that performance is not much of an
issue with a properly indexed record source, even if it contains millions of
records. Perhaps somebody with more detailed information on the subject
will weigh in here.
I just don't see that appending records to an archive table and deleting
those records from the current table makes sense unless the database has
become a monster.

"mscertified" wrote in message
...
Even if the data volume isn't 'excessive', you are still wasting resources
and adding to response time by processing these records every time you run
a
query. You are also risking that if the database corrupts you will lose
all
the records. In my opinion its much safer to offload the old unused
records
to another table.

-Dorian

"Cameron" wrote:

Well, the file size isn't excessive. And for the most part I think I
could
just hide the data so that the user only saw the past two years.

In one of my past jobs I was asked about split off data that pertained to
specific years due to accounting purposes. The accountant wanted to
physically have the front end and only the data pertaining to that year
to
see and to show the auditors. The senior programmer wrote a subroutine to
functionally build a new database and populate the tables with the prior
years data and then remove the prior years data from the current
database.

So even though Access is capable of 2 gig of storage there can still be
situations where you would need to split out the data to satisfy a higher
power such as Revenue Canada.

"BruceM" wrote:

If I understand the question correctly, the idea is to have just the
past
two years, in which case basing a form or report on a query with
DateAdd as
the criteria for a date field should work. Help has more information
about
the DateAdd function. This suggestion assumes that file size is not
excessive, as mentioned in another response.

"Cameron" wrote in message
...
I have about 10 years worth of data in a database, and i would like to
only
have 2 years. Is there a way to strip out the other 8 years of data
and
put
it in a seperate database?

Splitting out data is not a problem. You can devise a query to include only
records from a particular year, then export the information to a
spreadsheet. There are ways of doing that from the user interface of your
program. If this sounds as if it could be a useful technique for your needs
you can post a new thread. I have to admit I'm not that familiar with the
procedure of exporting. I can do it, but I'm not sure I'm doing it the best
way.
I'm not sure what you mean by "physically have the front end". The physical
structure of the data should be of no concern to the auditors, any more than
the physical location of ledger books. Then again, you never can tell with
auditors, especially ones who can only think in spreadsheet.

"Cameron" wrote in message
...
Well, the file size isn't excessive. And for the most part I think I could
just hide the data so that the user only saw the past two years.

In one of my past jobs I was asked about split off data that pertained to
specific years due to accounting purposes. The accountant wanted to
physically have the front end and only the data pertaining to that year to
see and to show the auditors. The senior programmer wrote a subroutine to
functionally build a new database and populate the tables with the prior
years data and then remove the prior years data from the current database.

So even though Access is capable of 2 gig of storage there can still be
situations where you would need to split out the data to satisfy a higher
power such as Revenue Canada.

"BruceM" wrote:

If I understand the question correctly, the idea is to have just the past
two years, in which case basing a form or report on a query with DateAdd
as
the criteria for a date field should work. Help has more information
about
the DateAdd function. This suggestion assumes that file size is not
excessive, as mentioned in another response.

"Cameron" wrote in message
...
I have about 10 years worth of data in a database, and i would like to
only
have 2 years. Is there a way to strip out the other 8 years of data and
put
it in a seperate database?