Changing Query Source

[EricF]

Hello, I was wondering if it is possible to change the source of a query in Access? (Similar to how you can reports)



Thanks

[Robeen]

You CAN, but I've had a couple of surprises when I've changed the data source of queries from one table/query to another.
Mainly my problem has been that I'd neglected to check whether the query I was modifying was being used by ANOTHER query - and then that other query broke.

You can go into SQL View and manually change the source Table/Query name for all fields in the query you want to change.
OR
You can use Design View to Add your new source table/query to your original query that you want to change the source for and then change the source table/query for each of your fields.

Wouldn't it be easier to create a new query?

[rpeare]

Can you tell us why you need to change table sources in a query? this is a big red flag that your database is not normalized and that you have several tables containing a lot of the same fields as one another.

[EricF]

I have a database that has multiple tables with the same fields, but populated with data from different years. (I intentionally wanted separate tables for each year.) The reason I want to copy queries is because some of them I would like to use with multiple tables and others I do not.

[rpeare]

Why do you want to intentionally keep them separate? If you are separating date by calendar or fiscal year you can do that by limiting what your queries show you rather than having to build the same query for every year table you have in your database. This is definitely a non-normalized table structure and it's causing you problems as you can see.

The easiest way to handle this is to re-normalize the data using a union query

SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
UNION ALL
etc...

then when you run your query instead of basing it on a different table, base it on a different date range so if you wanted a query for calendar year 2010 you would query this union query with the date range

Between #1/1/2010# and #12/31/2010#

Basically you are re-normalizing your data artificially so you can then produce a query that you don't have to modify at all, you just have to supply it with different date ranges.

[EricF]

I appreciate the input and suggestions. The problem I have is that the historical data that I have used old values (even though it's the same fields) that I no longer want to be valid going forward. Is there a way to allow a field to have old values in it, but force new values to be limited to whatever I determine?

[rpeare]

I don't understand the question, we're talking about a query, a union query. In each section of the union query you can limit the data you see, for instance if you wanted to only see data for calendar year 2004 that had a specific criteria and nothing else you would go to the 2004 section of your union query and put in a WHERE Clause picking out the data you want.

when you're talking about new values and old values it means nothing to me without some hint of your data structure and/or examples of the underlying data.

[EricF]

I was explaining the reason I have multiple tables structured the same. Here's an example... in a "State" field I might have historical values of TX and OH, but going forward I don't want to allow these states to be entered... only CA and NY. (This is a simplistic example to illustrate the question... I'm actually dealing with account codes that have changed, but I need to keep the historical data for reference.)

[rpeare]

That's fairly easy to do, you would just have to, on your 'state' table (assuming you have one) you'd just have to have an 'allowed' yes/no field then if a state is no longer allowed for data entry you would just filter your combo box or other data entry method by this yes no field and exclude anything that wasn't allowed. Which I think is what you were asking about doing in your previous post. Now that I re-read it in light of this explanation.