Variable table name in SQL

[dixie]

I want to write a query where one of the two tables in it is variable and
its name is picked up from a text box control on an open form. The query is
very simple and is only a select query with three fields, but the problem is
that one of its tables varies with the Calendar Year like tblYear2002,
tblYear2003, etc.

Is there a simple way of doing this?

dixie

[Bruce M. Thompson]

> I want to write a query where one of the two tables in it is variable and

its name is picked up from a text box control on an open form. The query is
very simple and is only a select query with three fields, but the problem is
that one of its tables varies with the Calendar Year like tblYear2002,
tblYear2003, etc.

Is there a simple way of doing this?

There is no simple way of doing things the hard way. You shouldn't have data
separated into separate tables like that based on any attribute. You should have
all the records in one table and filter the results of your query to return only
those records you require.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[gwarning!]

Like this?

DoCmd.RunSQL ("SELECT * from " & me!cboName & " WHERE thisistrue";)

dixie wrote:

I want to write a query where one of the two tables in it is variable and
its name is picked up from a text box control on an open form. The query is
very simple and is only a select query with three fields, but the problem is
that one of its tables varies with the Calendar Year like tblYear2002,
tblYear2003, etc.

Is there a simple way of doing this?

dixie

--
*** Remove SPLAT to email directly ***

[adil_a1]

Well, I am not sure if there are situations where you may need variable
table names, however below you will find a simple script where you can
really use variable table name!! I modified a script I found at
microsoft to simply run a select query for all user tables in a given
database. Hope this is of any use.
/*
SET NOCOUNT ON

DECLARE @TableName varchar(255),@execstr VARCHAR (255)

DECLARE TableName_cursor CURSOR
FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES where
Table_Type='BASE TABLE'
OPEN TableName_cursor

FETCH NEXT FROM TableName_cursor INTO @TableName

WHILE @@FETCH_STATUS=0
BEGIN
set @execstr='select * from '+ @TableName
EXEC (@execstr)
FETCH NEXT FROM TableName_cursor INTO @TableName
END

CLOSE TableName_cursor

DEALLOCATE TableName_cursor
*/

gwarning! wrote:

Like this?

DoCmd.RunSQL ("SELECT * from " & me!cboName & " WHERE thisistrue";)

dixie wrote:

I want to write a query where one of the two tables in it is variable and its name is picked up from a text box control on an open form. The query is very simple and is only a select query with three fields, but the problem is that one of its tables varies with the Calendar Year like tblYear2002, tblYear2003, etc.

Is there a simple way of doing this?

dixie

--
*** Remove SPLAT to email directly ***

[adil_a1]

Well, I am not sure if there are situations where you may need variable
table names, however below you will find a simple script where you can
really use variable table name!! I modified a script I found at
microsoft to simply run a select query for all user tables in a given
database. Hope this is of any use.
/*
SET NOCOUNT ON

DECLARE @TableName varchar(255),@execstr VARCHAR (255)

DECLARE TableName_cursor CURSOR
FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES where
Table_Type='BASE TABLE'
OPEN TableName_cursor

FETCH NEXT FROM TableName_cursor INTO @TableName

WHILE @@FETCH_STATUS=0
BEGIN
set @execstr='select * from '+ @TableName
EXEC (@execstr)
FETCH NEXT FROM TableName_cursor INTO @TableName
END

CLOSE TableName_cursor

DEALLOCATE TableName_cursor
*/

gwarning! wrote:

Like this?

DoCmd.RunSQL ("SELECT * from " & me!cboName & " WHERE thisistrue";)

dixie wrote:

I want to write a query where one of the two tables in it is variable and its name is picked up from a text box control on an open form. The query is very simple and is only a select query with three fields, but the problem is that one of its tables varies with the Calendar Year like tblYear2002, tblYear2003, etc.

Is there a simple way of doing this?

dixie

--
*** Remove SPLAT to email directly ***