470,638 Members | 1,510 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,638 developers. It's quick & easy.

Variable table name in SQL

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
4 13378
> 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
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

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

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.

Similar topics

3 posts views Thread by Geoff Winsor | last post: by
1 post views Thread by farooqazeem | last post: by
1 post views Thread by stephane | last post: by
2 posts views Thread by Kevin | last post: by
9 posts views Thread by Slickuser | last post: by
1 post views Thread by Korara | last post: by
???
1 post views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.