dynamically querying different tables based on a value in a combo box on a form?

[Conan Kelly]

Hello all,

Is it possible to set up a query to query different tables each time it is
run based on the value in a combo box on a form?

I have a Table Names table set up that contains all of the table names that
I would like to query. I have a form set up with a combo box bound to the
Table Names field in the Table Names table. I have a query set up that
pulls info from a single table. In the SQL view of the query, none of the
fields in the SELECT or WHERE statements have Table Qualifiers
([TableName].[FieldName]) (I don't know if Table Qualifiers is the correct
terminology--Please feel free to correct me if I'm wrong). They only have
field names ([FieldName]).

I tried changing the "FROM" statement to this: "FROM
[Forms]![frmTableNames]![cboTableNames]"

I have been able to do this in the past, but not in the way of changing what
table the query pulls from, only in the way of changing the criteria of
fields.

Is it possible to change which table a query pulls from based on a combo box
on a form?

Thanks for any help anyone can provide,

Conan Kelly

 

[Conan Kelly]

Duane,

Thanks for the feed back.

I guess you can say that I am the user. This DB is clients data they put
together in this format. We just need to extract the data and copy it into
Excel to crunch the numbers.

Since my boss & I are the only ones that are going to be using these DB's, I
don't want to go through the hassel of setting up their DB in a more
appropriate way.

I forgot about the UNION queries. I've used them quiet abit in SQL Server,
but I guess this being a different environment, it slipped my mind.

I'd really rather not go through the UNION query though. I was hoping that
selecting the table from a combo box on a form was possible (that would be
the prefered method), but I'm assuming that it is not.

For right now, I have the query open in SQL view and I'm pasting the table
name into the FROM statement every time I run it. I'm almost done with what
I need to do here, so I'll just continue this way.

Thanks again for all of your help,

Conan Kelly

Do you actually have multiple tables with the same basic fields? This
isn't
generally a good way to set up your database. The recommended solution is
to
have a single table with a column that identifies it's uniqueness from
other
records.

However, if they are the same, you can create a union query like:
SELECT *, "Table One" as TableName
FROM TableOne
UNION ALL
SELECT *, "Table Two"
FROM TableTwo
....;
Then create your query based on the union query where users can specify
the
appropriate TableName value.
--
Duane Hookom
Microsoft Access MVP

Hello all,

Is it possible to set up a query to query different tables each time it
is
run based on the value in a combo box on a form?

I have a Table Names table set up that contains all of the table names
that
I would like to query. I have a form set up with a combo box bound to
the
Table Names field in the Table Names table. I have a query set up that
pulls info from a single table. In the SQL view of the query, none of
the
fields in the SELECT or WHERE statements have Table Qualifiers
([TableName].[FieldName]) (I don't know if Table Qualifiers is the
correct
terminology--Please feel free to correct me if I'm wrong). They only
have
field names ([FieldName]).

I tried changing the "FROM" statement to this: "FROM
[Forms]![frmTableNames]![cboTableNames]"

I have been able to do this in the past, but not in the way of changing
what
table the query pulls from, only in the way of changing the criteria of
fields.

Is it possible to change which table a query pulls from based on a combo
box
on a form?

Thanks for any help anyone can provide,

Conan Kelly

 

[John Vinson]

Hello all,

Is it possible to set up a query to query different tables each time it is
run based on the value in a combo box on a form?

Not without rewriting the query's SQL using VBA code.

If you have many similarly-structured tables in your database...
*stop*. Storing data in table names is simply BAD DESIGN. Consider
instead putting all the data in one table, with an additional
(indexed, nonunique) field for whatever is now your tablename; you can
then very simply use a parameter query to extract that subset of the
data.

If you're worried about this making your table, or your database, too
big - don't. Each Table has a considerable amount of overhead storage;
if you have it in one table you get it all for one set of overhead,
now you have many.

John W. Vinson[MVP]

 

[John Nurick]

Hi Conan,

You can't get a query to pull a table name from a control on form.
Instead, you can write VBA code that assembles the necessary SQL
statement and assigns it to the QueryDef's SQL property. Something like
this, in the combo box's Change event procedure:

Dim strSQL As String

strSQL = "SELECT Field1, Field2 FROM " _
& Me.cboTableName.Value _
& " WHERE blah blah ORDER BY blah;"
CurrentDB.QueryDefs("MyQuery").SQL = strSQL


But: having multiple tables with the same structure and containing
subsets of the same data is usually a sign of incorrect database design:
in effect you are storing data in the table names. Normally one would
have all this data in a single table that also contains a field to store
whatever it is that the table names now signify.

And: on a point of terminology, the combo box you describe seems to be
unbound, with the TableNames table as its RowSource.

Hello all,

Is it possible to set up a query to query different tables each time it is
run based on the value in a combo box on a form?

I have a Table Names table set up that contains all of the table names that
I would like to query. I have a form set up with a combo box bound to the
Table Names field in the Table Names table. I have a query set up that
pulls info from a single table. In the SQL view of the query, none of the
fields in the SELECT or WHERE statements have Table Qualifiers
([TableName].[FieldName]) (I don't know if Table Qualifiers is the correct
terminology--Please feel free to correct me if I'm wrong). They only have
field names ([FieldName]).

I tried changing the "FROM" statement to this: "FROM
[Forms]![frmTableNames]![cboTableNames]"

I have been able to do this in the past, but not in the way of changing what
table the query pulls from, only in the way of changing the criteria of
fields.

Is it possible to change which table a query pulls from based on a combo box
on a form?

Thanks for any help anyone can provide,

Conan Kelly