Using a new table with old query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My company currently has an old database that contains data from 2002-2006.
We want to create a new one to go from june of 2005-june 2007. I created a
new table in the current database, but all my queries are gathering data from
the old table. How can I update all my queries to pull from the new table?
 
You might try erasing the contents of the old Tables, copying the
contents of the new ones into them, and then (with AutoCorrect enabled)
renaming the revised old Tables using the new names. (Did I mention
being sure to back up your database first?) You'll need to check to
ensure that the Queries were updated properly, but if it works as it
should, you'll save some work. Actually, you might not need to rename
the Tables, as the old Tables will now contain the new data and the
queries should work correctly.

Or you might try simply rewriting all the Queries. I usually do that,
in Query Design View, by adding the new Table to the upper pane, then
substituting its name for the old Table's name in the Table row below.
Then I open the SQL View to look for any that I missed (such as in
subqueries). If you have Access 2003 or later, you can display a list
of (most of) the Queries that make use of a given Table. (This misses
references like the contents of Union Queries or like SQL that you
compute in code, but it can still be quite helpful.)

If this is likely to happen again, you might try creating a Query that
reflects the Table's contents (e.g., "SELECT * FROM MyTable;") and use
its name in the other Queries instead of the Table's name. If you can
get that to work, updating in the future will be easier -- just redefine
the basic Query to point to the revised Table, and the other Queries
will refer to the new one.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Hi Tammy,

This is exactly the problem that comes from using data as field or table
names...whenever you change an object's name, every reference to the
object in a query, procedure, etc. must be found and changed also. This
is not considered properly normalized database design; it would be far
better to have a single table with a date field than multiple tables of
identical design but containing data from different years (e.g.
tbl2000-2001, tbl 2002-2003, tbl 2003-June 2004 etc.). You could then
query the single table for the data from the desired periods, and not
have to change queries etc. every time. If you have a good reason for
archiving the older data, and plan to do it on a regular basis, you
might consider investing (we're not talking big $$, here...only about
$30 US) in an add-in tool such as Rick Fisher's Find and Replace (see
http://www.rickworld.com/products.html). There's another one called (I
think) SpeedFerret, but I haven't tried it myself.

hth,

LeAnne
 
Back
Top