Deleting records older than a year

[Afrosheen via AccessMonster.com]

Thanks again for taking the time to read this.

I have a table called tblTaps. It has a yes/no and a date entered field in it,
besides other fields. Those are the ones I'm concerned about.

What I want to do is after about 3 years I want Access to delete these
records. I found a routine on here that goes by 7 days. But I want to go out
farther. Here's the code:

DELETE * FROM TableName WHERE FieldName <= Date()-7;

What I would have to check on is two variables. The yes/no and the date
entered field would have to checked on to see if they meet the criteria. I'd
like to set it up so a box would come up after 30 days stating something like
"You need to perform your yearly maintenance". That part I can do.

I'm just not sure about the code for deleting the records older than 3 years.

Any suggestions would be greatly appreciated.

Thank you for any help.

 

[Graham Mandeno]

Hi Afrosheen

You can calculate the date three years ago using the DateAdd function:

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

So your modified SQL statement would look like this:

DELETE * FROM TableName
WHERE DateField <= DateAdd( "yyyy", -3, Date() )
AND YesNoField <> 0;

You didn't say how you want to check the yes/no field. This will delete old
records only if the yes/no field is "Yes". If you want the opposite then
change the <> to =.

 

[Afrosheen via AccessMonster.com]

Thanks for your reply Graham. The yes/no is just a field that I set up so
when I look at a persons records they won't show. For example if the record
is "Archived" then the yes/no field will be set true. So I just needed
something where I would look at records with yes/no set to false. But, when
it comes time to delete the records then the yes/no = true will be deleted as
well as where the date is 3 yrs back.

Thanks for the help. I'll give it a test probably tomorrow, Wednesday and get
back to you.

Hi Afrosheen

You can calculate the date three years ago using the DateAdd function:

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

So your modified SQL statement would look like this:

DELETE * FROM TableName
WHERE DateField <= DateAdd( "yyyy", -3, Date() )
AND YesNoField <> 0;

You didn't say how you want to check the yes/no field. This will delete old
records only if the yes/no field is "Yes". If you want the opposite then

Thanks again for taking the time to read this.

[quoted text clipped - 22 lines]

Thank you for any help.

 

[sfisher973]

Deleting records in a database is rarely a good Idea I recommend archiving
them offline instead.
--
-Steve

Thanks for your reply Graham. The yes/no is just a field that I set up so
when I look at a persons records they won't show. For example if the record
is "Archived" then the yes/no field will be set true. So I just needed
something where I would look at records with yes/no set to false. But, when
it comes time to delete the records then the yes/no = true will be deleted as
well as where the date is 3 yrs back.

Thanks for the help. I'll give it a test probably tomorrow, Wednesday and get
back to you.

Hi Afrosheen

You can calculate the date three years ago using the DateAdd function:

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

So your modified SQL statement would look like this:

DELETE * FROM TableName
WHERE DateField <= DateAdd( "yyyy", -3, Date() )
AND YesNoField <> 0;

You didn't say how you want to check the yes/no field. This will delete old
records only if the yes/no field is "Yes". If you want the opposite then

Thanks again for taking the time to read this.

[quoted text clipped - 22 lines]

Thank you for any help.

 

[Afrosheen via AccessMonster.com]

It may be true, but wouldn't the database get bloated? The yes/no field that
I mentioned is for Archiving. What that basically does is if the yes/no field
is true then the records are "archived" {not shown} when viewing.

You're suggesting archiving offline. Please explain and/or how I would go
about doing this.

Thanks.

Deleting records in a database is rarely a good Idea I recommend archiving
them offline instead.

Thanks for your reply Graham. The yes/no is just a field that I set up so
when I look at a persons records they won't show. For example if the record

[quoted text clipped - 27 lines]

 

[sfisher973]

Well... It depends somewhat on if you are using an Access backend or some
other backend. Your data is stored in a separate db from your access code
isn't it?

What makes it off-line is that the records are stored in a separate DB that
is not connected to you application, but you could retrieve them if you
really had to.

1) You could use the built-in Access backup DB command to make a dated /
compacted backup of the entire access (or other db) database before deleting
the records. Recommended anyway in case you make a mistake.

2) Make a new db and then export the table structure into it (not the data).
Then temporarily attach to the table in the new archive db and run an append
query with your criteria set to select the records older than 3 years into
the attached table. When you're done appending the records in archive then
you delete tha same records from the original db.

Of course this could all be programmed with a bit of work. I wouldn't be
surprised to find that someone has some free code that will do most of it
already...

3) Depending on the number of records you might be able to just cut and
paste the records between the two databases

 

[Afrosheen via AccessMonster.com]

The data will be on a BE system. New records are added 3x a year. In the
original table there is about 300 records. So that means that for every
record in 3 years would be: 2,700 records. That's why I kinda waned after 3
years the first record would be dropped off.

I'll look for some routine that would move the record to another


Thanks for the come back.

 

[JimBurke via AccessMonster.com]

What you really should do is take regular backups of the database that has
your tables. This would then be your archiving system. There's nothing wrong
with deleting records as long as you have archived data somewhere. In this
particular case, I wouldn't be worried about 2700 records - unless they are
amazingly huge. It's perfectly normal for a table to have MANY more rows than
that.

The data will be on a BE system. New records are added 3x a year. In the
original table there is about 300 records. So that means that for every
record in 3 years would be: 2,700 records. That's why I kinda waned after 3
years the first record would be dropped off.

I'll look for some routine that would move the record to another

Thanks for the come back.

Well... It depends somewhat on if you are using an Access backend or some
other backend. Your data is stored in a separate db from your access code

[quoted text clipped - 20 lines]

3) Depending on the number of records you might be able to just cut and
paste the records between the two databases

 

[Afrosheen via AccessMonster.com]

Thanks again for the information Jim. I do have a routine where a subform
pops up if the day is #7 and creates a backup. It's just set up for a normal
backup not a FE or BE one. It's not one I wrote. It's one I picked up and
written by: Dev Ashish from The Access Web {API I believe}. so until I find
something else I'll have to use that.

What you really should do is take regular backups of the database that has
your tables. This would then be your archiving system. There's nothing wrong
with deleting records as long as you have archived data somewhere. In this
particular case, I wouldn't be worried about 2700 records - unless they are
amazingly huge. It's perfectly normal for a table to have MANY more rows than
that.

The data will be on a BE system. New records are added 3x a year. In the
original table there is about 300 records. So that means that for every

[quoted text clipped - 10 lines]

 

[John W. Vinson]

The data will be on a BE system. New records are added 3x a year. In the
original table there is about 300 records. So that means that for every
record in 3 years would be: 2,700 records. That's why I kinda waned after 3
years the first record would be dropped off.

When you get to 2,700,000 records you can start to get concerned. Access won't
even blink at 2700; that's *tiny*.

 

[Afrosheen via AccessMonster.com]

Thanks John. That was a good and much needed laugh....

 

[John W. Vinson]

Thanks John. That was a good and much needed laugh....

<g> Glad to help, friend.