Replacing Tables in a Query

[Access User]

I analyze general New York state information and to do so I have a table for
that state. I also have thousands of queries dedicated to that state. Now I
want to analyze another state. So I set up a new table for the new state. For
each individual query I have done a "Save As" of the New York state query for
the new state and then gone to SQL View and change the state name. For
example, from NY to TX. The problem is when I have a specific New York state
query that feeds from 30 other New York state queries.

How do I replace the several queries from NY state to the other other state?
What I have been doing is the following:
1) Open the New York state query
2) Do a "Save As" of the query and save it with the new state name
3) View it through SQL View; I tried to do a Find and replace, but this does
not work in Access
3) I go through the SQL information and each time I find "NY" I delete it
and change it to "TX;" this takes me forever to do

In a query is there a way I can just do a FIND "NY" and REPLACE it with
"TX," so it doesn't take me so long?

 

[DougY]

How about having the data in one table with a [State] field... this way all
you'll need is to change the WHERE clause to query the data of a different
state... if you create a "View Query" to base all you other queries on (vs.
the table), all you need is to change the state value in one place.

Other options:
- copy and paste each SQL into a text editor; Find and Replace the table
name; paste back to Access SQL Pane.

- Cycle through the QueryDef in code and use Replace() to change the table
name and save the changes

The first option I suggested is the least amount of work, and the easiest to
maintain... not to mention it's conforming better to proper normalization.

 

[Lord Kelvan]

to be honest you should have a state table then a details table

state table stores

NY
TX
etc

and details has the id for the state table and the relevant details

but i do supsoe at this time it is far too late for that

and doing that you would have to create a massive multitude of queries
you could have just had a paramater in each where you could enter the
state

you could try doing it through vba to read in each query then alter it
and save it as a new one but i dont know that code if there is any so
you coudl try researching creating queries with vba or something

Regards
Kelvan

 

[Peter Doering]

In a query is there a way I can just do a FIND "NY" and REPLACE it with
"TX," so it doesn't take me so long?

Dim Db As DAO.Database
Dim Qdf As DAO.QueryDef

Set Db = CurrentDb

For Each Qdf In Db.QueryDefs
Qdf.SQL = Replace(Qdf.SQL, "NY", "TX")
Next Qdf

Set Qdf=Nothing
Set Db=Nothing

(aircode)

Be aware that the replace statement will not distinguish between "NY" and
e.g. "ANY". Make sure you choose a unique pattern.