By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,105 Members | 1,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,105 IT Pros & Developers. It's quick & easy.

Variable table name in SQL

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
> 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.<<
Nov 12 '05 #2

P: n/a
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 ***

Nov 12 '05 #3

P: n/a

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 ***


Nov 13 '05 #4

P: n/a

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 ***


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.