Annual update of Queries

Is there a shortcut to updating the table name that is in all of my Queries?
Each year we start with a new table again and I have to go in and manually
change every field of every query from the old table name to the new table
name.

Surely there is an automated way to do this. Anybody know?
Thanks.

Why do you start with a new table? That is not a normalized design. If you
include the date (or year) in your records, then a new table is not needed.

Because this is not a normal way to do things, there is probably not an easy
way to make the changes. Perhaps one of the others will have some tricks
though.

--
Hope that helps!

RBear3
..

"mimf" wrote in message
...
Is there a shortcut to updating the table name that is in all of my
Queries?
Each year we start with a new table again and I have to go in and manually
change every field of every query from the old table name to the new table
name.

Surely there is an automated way to do this. Anybody know?
Thanks.

Well you found one good reason NOT to have a table for every year. Rather you
should have all the same kind of data in one table. Then have a date/time
field in the table so that you can tell the year of the data.

Fix it now and you won't have these problems in the future.

I can't think of any automated way of doing this. Possibly Rich Fisher's
excellent Find and Replace might be just the ticket.
http://www.rickworld.com/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"mimf" wrote:

Is there a shortcut to updating the table name that is in all of my Queries?
Each year we start with a new table again and I have to go in and manually
change every field of every query from the old table name to the new table
name.

Surely there is an automated way to do this. Anybody know?
Thanks.

You've described 'how' you want to do something, but not described 'why' it
has to be that way.

Other responders have pointed out that having one table per year is not a
well-normalized design. "Who cares?" you might ask...

If you want to get the best use of Access' relationally-oriented
features/functions, you care!

Good luck.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"mimf" wrote in message
...
Is there a shortcut to updating the table name that is in all of my
Queries?
Each year we start with a new table again and I have to go in and manually
change every field of every query from the old table name to the new table
name.

Surely there is an automated way to do this. Anybody know?
Thanks.

You could alias the table name in the query and then all you have to do
is update one value.

SELECT A.Field1, A.Field2, A.Field3
FROM Table2008 as A
WHERE A.Field1 = "simon"


Then all you need to do is open the query in SQL view and change
Table2008 to Table2009


SELECT A.Field1, A.Field2, A.Field3
FROM Table2009 as A
WHERE A.Field1 = "simon"

This is NOT the best solution, but if you can't redesign your table
structure then this may make life a bit easier.

The first time you do this, open the query in design view and click on a
table and select properties and in the properties give the table an
alias. This will automatically propagate for most of the field
references. It won't fix expressions.

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


mimf wrote:
Is there a shortcut to updating the table name that is in all of my Queries?
Each year we start with a new table again and I have to go in and manually
change every field of every query from the old table name to the new table
name.

Surely there is an automated way to do this. Anybody know?
Thanks.