Changing query to access different tables

[Marizel]

I'm not sure there's an easy solution to this, but thought I'd ask. I
often find myself with a query which I'd like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select *"
is not really an option.

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

For an example, if I have the following query that reads from the 2003
data:
SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)="WV"));

What I'd like is a quick way to now convince it to select the same
fields, but from tbl_MD_04 (or tbl_MD_02, etc.).

Any ideas?
Thanks,
Mary

I tried searching for a solution to this, but didn't

[Pieter Linden]

ma*****@wvnet.edu (Marizel) wrote in message news:<49**************************@posting.google. com>...

I'm not sure there's an easy solution to this, but thought I'd ask. I
often find myself with a query which I'd like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select *"
is not really an option.

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

For an example, if I have the following query that reads from the 2003
data:
SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)="WV"));

What I'd like is a quick way to now convince it to select the same
fields, but from tbl_MD_04 (or tbl_MD_02, etc.).

Any ideas?
Thanks,
Mary

Sounds like a flawed design, because you should probably have a single
table with an extra field that flags the records. If you do that,
then you could just turn this into a parameterized query, and you'd be
off and running. As is, you have to do something like:
1. open the querydef.SQL in code
2. use the Replace function to replace each occurence of the old
tablename with the new one.
3. run the query. (I guess you could save this as a new query...)

Umm... convinced your design is a bit off yet? If you just included
one extra field in your table, you could just do something like point
your query at an unbound form to gather parameters and then open your
query. No code required.

[John Baker]

Marizel wrote:

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

Are you using the queries directly, or as datasources for forms and
reports? If the latter, just create all the queries you need, and you
can manually or programtically change the datasource for forms and reports.

[Pieter Linden]

ma*****@wvnet.edu (Marizel) wrote in message news:<49**************************@posting.google. com>...

I'm not sure there's an easy solution to this, but thought I'd ask. I
often find myself with a query which I'd like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select *"
is not really an option.

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

For an example, if I have the following query that reads from the 2003
data:
SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)="WV"));

What I'd like is a quick way to now convince it to select the same
fields, but from tbl_MD_04 (or tbl_MD_02, etc.).

Any ideas?
Thanks,
Mary
Okay, after a little more useful thought... why are you partitioning
your data by year into different tables? Don't. Just chuck it all in
ONE table with a Year field or whatever. then this whole thing is so
easy it's ridiculous.

Put all your criteria in unbound textboxes/comboboxes on a form and
then just do something like this:
SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)=Forms![frmSearchCriteria]![cboState]));

then put a button on your form to open the query. Done.

[corey lawson]

pi********@hotmail.com (Pieter Linden) wrote in
news:bf**************************@posting.google.c om:

ma*****@wvnet.edu (Marizel) wrote in message
news:<49**************************@posting.google. com>...

I'm not sure there's an easy solution to this, but thought I'd ask.
I often find myself with a query which I'd like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select
*" is not really an option.

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

For an example, if I have the following query that reads from the
2003 data:
SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)="WV"));

What I'd like is a quick way to now convince it to select the same
fields, but from tbl_MD_04 (or tbl_MD_02, etc.).

Any ideas?
Thanks,
Mary

Okay, after a little more useful thought... why are you partitioning
your data by year into different tables? Don't. Just chuck it all in
ONE table with a Year field or whatever. then this whole thing is so
easy it's ridiculous.

Put all your criteria in unbound textboxes/comboboxes on a form and
then just do something like this:

SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE
((([tbl_MD_03].State)=Forms![frmSearchCriteria]![cboState]));

then put a button on your form to open the query. Done.

Maybe he's getting ready to upsize it to SQL Server (7/2K), in which
case, this vertically partitioned data is more than easily wrapped up in
one view. It's pretty dang fast. Yes, I have this scenario, with a
dataset with over 2 million rows in it. Breaking it up into a few smaller
pieces, and it works great now. Of course, in Oracle, the partitioning is
invisible to the user, and it can readjust itself within reason (usually
based on a time-based field...), so your updates/inserts still go against
only one target entity. In SS, you need to have a stored proc or
something that adds data to each subtable (or use an INSTEAD OF view in
SS2K).

[Marizel]

John Baker <ba*****@ix.netcom.com> wrote in message news:<7R*********************@fe2.columbus.rr.com> ...

Marizel wrote:

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

Are you using the queries directly, or as datasources for forms and
reports? If the latter, just create all the queries you need, and you
can manually or programtically change the datasource for forms and reports.

I'm afraid the queries are used directly, to export data to different
formats--delimited text, dbf, Excel, you name it.

[Bob Quintal]

ma*****@wvnet.edu (Marizel) wrote in
news:49**************************@posting.google.c om:

John Baker <ba*****@ix.netcom.com> wrote in message
news:<7R*********************@fe2.columbus.rr.com> ...

Marizel wrote:

> Is there an easy way to change the source of a query,
> either in the design grid or SQL display? I suppose I
> could copy the SQL into WordPad and use find and replace,
> but it seems Access should provide some way to do this.

Are you using the queries directly, or as datasources for
forms and reports? If the latter, just create all the
queries you need, and you can manually or programtically
change the datasource for forms and reports.

I'm afraid the queries are used directly, to export data to
different formats--delimited text, dbf, Excel, you name it.

If the field names are constant, you can use the Field List
Properties Alias property to change the name. Open the query
builder click on the first field list and click on the properties
icon in the toolbar. there are two fields alias and source. Just
type the new table's name, and proceed to do the remainder of the
tables.

Now select SQL view. You'll see the field names prefixed with what
you've entered as alias and the FROM statements read something like
Foo AS Bar. just delete the Foo AS and save your query. When you
reopen it, it'll use the new tables.
--
Bob Quintal

PA is y I've altered my email address.

[Marizel]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

ma*****@wvnet.edu (Marizel) wrote in message news:<49**************************@posting.google. com>...

I'm not sure there's an easy solution to this, but thought I'd ask. I
often find myself with a query which I'd like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select *"
is not really an option.

Okay, after a little more useful thought... why are you partitioning
your data by year into different tables? Don't. Just chuck it all in
ONE table with a Year field or whatever. then this whole thing is so
easy it's ridiculous.

Put all your criteria in unbound textboxes/comboboxes on a form and
then just do something like this:

SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)=Forms![frmSearchCriteria]![cboState]));

then put a button on your form to open the query. Done.

Well, there aren't really any forms involved. Maybe I'd better
explain a little further what I'm doing here. I kind of
oversimplified in the example. Afraid it's going to be kind of long.

Another organization has a large, lovely, mostly normalized relational
database of health professionals licensure info. My organization is
contracted to take requests from people who want a copy of portions of
this data. Nobody wants the original lovely 27 normalized linked
tables, they want a flat file to read into some system of their own.
So the original organization squashes it into a single table database,
and sends it to me quarterly. (Well, actually it's 3 tables for 3
different health professions, but that just complicates it further.)
So, anyhow, I have a new set of data--a snapshot at than moment, not
really something I can throw in with the previous quarter.

When I get the data, I fill the multiple requests. Some people want
their data in Access, others in Excel, delimited text, fixed field
text, dbf files, printed out, mailing labels, you name it. They can
select certain fields they want and also if they want the whole list
or limited to only physicians from certain counties, or with certain
specialties, etc.

So when someone orders data I set up a query for what they want and
then use export to create their dataset. For people who order
regularly I try to reuse the queries. Usually I can just name last
quarter's MD table to somethink like MD_Y04Q1 and link the new one as
MD, and things are fine. But right now, the original organization is
changing their database system around repeatedly and I need to do an
initial query to massage the data a little before it gets to my 50
little queries. Should I have based all those on an initial query to
start with? You bet I should, but now I'm stuck with changing it.

Access isn't much help when it can't find something and adds the Expr
stuff either: SELECT [tbl_MD_03].LastName as Expr1,
[tbl_MD_03].FirstName as Expr2, etc. That limits using WordPad to
edit the table name.

Anyhow, sorry for the length, if you're still with me. This is kind
of a special situation now, but I freqently find myself needing to
change query source for one reason or another in other situations,
i.e. trying to make a copy of a query, and it's always a pain.

Thanks,
Mary

[Marizel]

Bob Quintal <rq******@sPAmpatico.ca> wrote in message news:<Xn**********************@66.150.105.50>...

ma*****@wvnet.edu (Marizel) wrote in
news:49**************************@posting.google.c om:

> Is there an easy way to change the source of a query,
> either in the design grid or SQL display? I suppose I
> could copy the SQL into WordPad and use find and replace,
> but it seems Access should provide some way to do this.

If the field names are constant, you can use the Field List
Properties Alias property to change the name. Open the query
builder click on the first field list and click on the properties
icon in the toolbar. there are two fields alias and source. Just
type the new table's name, and proceed to do the remainder of the
tables.

Now select SQL view. You'll see the field names prefixed with what
you've entered as alias and the FROM statements read something like
Foo AS Bar. just delete the Foo AS and save your query. When you
reopen it, it'll use the new tables.

Perfect (well almost). This is just what I was looking for!

The only not quite perfect part is that if you spell the new table
name incorrectly you're stuck with:
SELECT [tbl_oops].LastName AS Expr1, [tbl_oops].FirstName AS Expr2,
.....
and seemingly without any easy way out of it.

Thanks,
Mary

[Bob Quintal]

ma*****@wvnet.edu (Marizel) wrote in
news:49**************************@posting.google.c om:

Bob Quintal <rq******@sPAmpatico.ca> wrote in message
news:<Xn**********************@66.150.105.50>...

ma*****@wvnet.edu (Marizel) wrote in
news:49**************************@posting.google.c om:

>> > Is there an easy way to change the source of a query,
>> > either in the design grid or SQL display? I suppose I
>> > could copy the SQL into WordPad and use find and
>> > replace, but it seems Access should provide some way to
>> > do this.
> If the field names are constant, you can use the Field List
Properties Alias property to change the name. Open the query
builder click on the first field list and click on the
properties icon in the toolbar. there are two fields alias
and source. Just type the new table's name, and proceed to do
the remainder of the tables.

Now select SQL view. You'll see the field names prefixed with
what you've entered as alias and the FROM statements read
something like Foo AS Bar. just delete the Foo AS and save
your query. When you reopen it, it'll use the new tables.

Perfect (well almost). This is just what I was looking for!

The only not quite perfect part is that if you spell the new
table name incorrectly you're stuck with:
SELECT [tbl_oops].LastName AS Expr1, [tbl_oops].FirstName AS
Expr2, ....
and seemingly without any easy way out of it.

Always work on a copy of your query :)

Thanks,
Mary

--
Bob Quintal

PA is y I've altered my email address.

[Pieter Linden]

this isn't terribly hard, but more complicated than you might think.
Here's how I did it.

I have a form with the following controls on it:
(cbo = combobox, txt=textbox, cmd=commandbutton)
cboQuery, txtChangeFrom, txtChangeTo, txtOldSQL, txtNewSQL, cmdGetSQL,
cmdChangeSQL, txtNewQueryName, cmdSaveSQL, cmdListControls
Here's the code behind all my buttons:
Private Sub cmdGetSQL_Click()
'retrieves the SQL of the query selected from the combobox listing
the queries in the db.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs(Me.cboQuery)

Me.txtOldSQL = qdf.SQL

Set qdf = Nothing
Set db = Nothing
End Sub
Private Sub cmdChangeSQL_Click()
'modifies the SQL in the txtOldSQL textbox (on the form), and puts
it into txtNewSQL textbox

Dim strSQLOld As String, strSQLNew As String

strSQLOld = Me.txtOldSQL
strSQLNew = Replace(strSQLOld, Me.txtChangeFrom, Me.txtChangeTo)

Me.txtNewSQL = strSQLNew
End Sub
Sub CreateQueryDefX(ByVal strOldQryName As String, ByVal strNewQryName
As String, _
ByVal strOldSource As String, ByVal strNewSource
As String)

'pretty much copied most of this right out of the help file
'creates the new querydef from the modified SQL from
cmdChangeSQL_Click

Dim dbs As Database
Dim qdfOld As QueryDef
Dim qdfNew As QueryDef
Dim strSQLOld As String, strSQLNew As String

Set dbs = CurrentDb

Set qdfOld = dbs.QueryDefs(strOldQryName)
strSQLOld = qdfOld.SQL

'--since you can't save over the old query, you need to delete it
after grabbing the old SQL...
If strOldQryName = strNewQryName Then
dbs.QueryDefs.Delete (strOldQryName)
End If

strSQLNew = Replace(strSQLOld, strOldSource, strNewSource, 1, ,
vbTextCompare)
' Create permanent QueryDef.
Set qdfNew = dbs.CreateQueryDef(strNewQryName, strSQLNew)
' refresh the collection
dbs.QueryDefs.Refresh

Set qdfOld = Nothing
Set qdfNew = Nothing
Set dbs = Nothing
End Sub
Private Sub cmdSaveSQL_Click()
'this is the button on my form.
Call CreateQueryDefX(Me.cboQuery, Me.txtNewQueryName,
Me.txtChangeFrom, Me.txtChangeTo)
End Sub

Private Sub cmdListControls_Click()
'just for listing all the controls on this form that I'm
interested in (all but labels).
Dim ctl As Control
Dim strControlList As String

For Each ctl In Me.Controls
If TypeOf ctl Is Label Then
'do nothing
Else
strControlList = strControlList & ", " & ctl.Name
End If
Next ctl

'snip off initial ", "
strControlList = Right$(strControlList, Len(strControlList) - 2)

Me.txtNewSQL = strControlList

End Sub

If you want, I can send you the form and all the code for it in one
DB, then you can just import the forms/code and just use it. (May be
too hard to explain if you've never done this before!)

HTH,
Pieter