Change target for a query?

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

Guest

Hi,We have an events database, with a number of queries to extract subsets
of data from the whole year events table.
We are now finished with Events_2004, and have created a new table
Events_2005.
Is it possible to use the existing queries and point them to the new table,
or do we have to re-create all the queries?
Thanks
Leigh
 
if the two tables have all the same fields ...

first, make a copy of your query and rename (Events_2005 Query)

open Events_2005 Query in design view

add Events_2005 table (query menu, show table)

for each field in the query, change the information in Table: using the drop
down menu found below each Field: (you'll see these words to the extreme left)

there should be no need to add/delete fields or modify the exisiting criteria

then, delete Events_2004 table (right click on the table, remove table)
 
This may not be the most elegant method, but in your situation I open the
query in SQL view. Then I copy the entire query and paste it into Notepad.
Then I do a Find/Replace and change the table name. After that, I copy the
text from Notepad and paste it back into SQL view.

I'm sure it's not the ideal way, but it works for me.

I would save a copy of your query before trying anything, though.
 
Yes, you will have to re-create the queries. Also you will have duplicate the
forms, reports or standard modules.

It would be better to normalize your database. All you need to do is add an
additional field that is the year of the event. Don't use "Year" or "Date" as
the field name - they are reserved words in Access.

You will have to change the forms, reports, code, and queries but only once.
The queries will have a criteria to select the events for a year. You can use
a control to enter the year of interest.... then you won't need to keep
adding tables/queries for each additional year.


Steve
 
Hi
Thanks you for your replies, I will try both methods and see how it goes.
Great to have such fast and helpful replies in this forum
A Happy New Year to all
Leigh
 
Hi Steve,
Thanks for the further advice, I think in the long term this must be the way
to go,as it avoids messy conversions every year, I will see if we can
implement this solution
Thanks agai
Leigh
 
Back
Top