If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Annual update of Queries
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. |
#3
|
|||
|
|||
Annual update of Queries
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. |
#4
|
|||
|
|||
Annual update of Queries
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. |
#5
|
|||
|
|||
Annual update of Queries
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. |
Thread Tools | |
Display Modes | |
|
|