I have a database that has 2008 and 2009 records.  I want to delete the 2008 
queries.  Is there a way to delete multiple queries at the same time?  It 
takes so long to delete them 1 by 1.  
Also, at the end of each year I have to copy the database and change the 
name to the new year, now is 2010.  Then I have to go into that database and 
reconfigure all the queries to use 2010 instead of 2009, for example.  After 
I reconfigure the new years queries, having them look towards the 2010 
database instead of 2009, I have to delete the old year's records, tables and 
queries.  Is there a better way to do this?
Thank-you so much for any help you can give me.

Why rename the Database?  Use a name that is date insensitive, and it 
will always be current.  IE:  SalesData rather then SalesData2009.  You 
no longer have to copy the database each year.  You simply continue to 
use the same one.  You could make a complete copy at the end of the year 
for archiving, (SalesData2008, SalesData2009, but would not have to redo 
your current database.

Instead of deleting and rewriting the queries, have them use the current 
year, and Current Year -1, whatever it happens to be, as the basis. 
This year and last year.  Then you have to change nothing, as teh year 
changes, so do your queries.  Or, you could use a single record, single 
field control table to keep the year in, and base all of your queries 
off of that.  Simply change that record from 2009 to 2010, and you are 
ready to go.

Build a macro that in turn calls a single query for each table that 
needs to be purged, that automatically deletes every record more then 
two years from your control record, or two years from the current year, 
whichever you decide to go with.

Hardcoding things that change into a database is generally bad design, 
because you have to go through the kind of hassle you are now having to 
go through whenever something changes.  Instead, make it flexible, so 
that you can make one change in one place and cover everything that 
needs to be changed.

Phil





On 1/5/2010 10:06 AM, Pat Hughes wrote:
> I have a database that has 2008 and 2009 records.  I want to delete the 2008
> queries.  Is there a way to delete multiple queries at the same time?  It
> takes so long to delete them 1 by 1.
> Also, at the end of each year I have to copy the database and change the
> name to the new year, now is 2010.  Then I have to go into that database and
> reconfigure all the queries to use 2010 instead of 2009, for example.  After
> I reconfigure the new years queries, having them look towards the 2010
> database instead of 2009, I have to delete the old year's records, tables and
> queries.  Is there a better way to do this?
> Thank-you so much for any help you can give me.

Yes there is a better way.

Redesign your table structure so that there is a field that defines the year 
for each record and keep the data for all years in the same table.  Then all 
you need to do is use that field to filter the appropriate data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Pat Hughes wrote:
> I have a database that has 2008 and 2009 records.  I want to delete the 2008 
> queries.  Is there a way to delete multiple queries at the same time?  It 
> takes so long to delete them 1 by 1.  
> Also, at the end of each year I have to copy the database and change the 
> name to the new year, now is 2010.  Then I have to go into that database and 
> reconfigure all the queries to use 2010 instead of 2009, for example.  After 
> I reconfigure the new years queries, having them look towards the 2010 
> database instead of 2009, I have to delete the old year's records, tables and 
> queries.  Is there a better way to do this?
> Thank-you so much for any help you can give me.