Prompt for table when running query

B

Bob

So I have dug myself a hole, I have a query, OverviewQ, that has a bunch of
SELECT expressions that are running off of table/ FROM [GradNurse Main]. Now
that I have filtered the data on table [GradNurse Main] using queries into
five seperate queries, InitialQ, 3moQ, 6moQ, 9moQ and >1yrQ, Is there any way
to apply the SELECT expressions of query OverviewQ to the records in these
new queries, if the fields have not changed except for the table/query in the
FROM clause? Possibly a pop-up field asking for what data set to run the
expressions on, or am I going about this in an ignorant fashion? Please show
me the light!
 
W

Wayne Morgan

If the fields are the same, you could make a copy of your current query and
change the table name in the FROM clause to the name of one of the other
queries. Where the problem comes in is that if Access made the query, it
also included the table name on every field (i.e. TableName.FieldName). You
will also need to change the name here. The table name doesn't need to be
attached to the field name unless you have more than one table/query in the
FROM clause and the field name exists in more than one of those
tables/queries.
 
W

Wayne Morgan

Rereading the title of your message, if you want to dynamically change the
query you can do so using code. Create the SQL string in code and assign it
to the SQL property of the query. You could then use code and/or controls on
a form to prompt for the information.

Example:
strSQL = SELECT Field1 FROM " & Me.txtTableName & ";"
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL
 
J

John Vinson

So I have dug myself a hole, I have a query, OverviewQ, that has a bunch of
SELECT expressions that are running off of table/ FROM [GradNurse Main]. Now
that I have filtered the data on table [GradNurse Main] using queries into
five seperate queries, InitialQ, 3moQ, 6moQ, 9moQ and >1yrQ,
Click to expand...

It sounds to me like *ONE* Parameter query would do the job here, if
the only difference between these queries is a criterion. How do they
differ otherwise?

John W. Vinson[MVP]
 
B

Bob

Thanks a lot guys you are steering me in the right direction, but when I said
I dug a Hole I really meant it, I have some insame, 100 columns, and to
change the FROM
in all of those columns for every query I create
could be a VERY long process. Is there a way I could go though this query
once and then upon applying all of these SELECT expressions to a new query
simply input once the query in which it should pull from?

John Vinson said:
So I have dug myself a hole, I have a query, OverviewQ, that has a bunch of
SELECT expressions that are running off of table/ FROM [GradNurse Main]. Now
that I have filtered the data on table [GradNurse Main] using queries into
five seperate queries, InitialQ, 3moQ, 6moQ, 9moQ and >1yrQ,
Click to expand...

It sounds to me like *ONE* Parameter query would do the job here, if
the only difference between these queries is a criterion. How do they
differ otherwise?

John W. Vinson[MVP]
Click to expand...
 
W

Wayne Morgan

The way you've worded this, I'm not completely sure about what you're trying
to do. However, making the changes to the query should be relatively easy
using code. You would need to get the SQL of the query, use the Replace()
function to change the desired word, then write the new SQL back to the
query.

Example:
Dim strSQL As String, db As DAO.Database, qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")
strSQL = qdf.SQL
strSQL = Replace(strSQL, "tblTable1", "tblTable2")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing
 
Top