Table selections

[Rob]

Hello..

I've been searching for an answer to my query question in here before
posting my inquiry, and I've found a couple. However, I cant see any straight
answers to other people's questions so I'll be asking the same question and
hopefully, someone can help me.

I have these monthly tables in the AS/400 mainframe that contains millions
of records (thats the reason why they were setup as monthly tables). The
individual monthly tables are linked via ODBC (ie tblJan03, tblFeb03 ..
tblAug05). I want to create a single query where I can prompt the user's to
input the tablename they wish to use (ie tblFeb03 or tblJan05, etc) or even
possibly multiple months (ie tblJan05 thru tblMar05). In this query, there
will be another parameter to search for specific days or range of dates. For
example:

Select X1,X2 from tblJan05 where Inv_Date between 20050101 and 20050331

Union query will not work as the monthly table name is variable. I thought
of VB but I'm a newbie to VB and knows little on how to write codes.

Any ideas will be much appreciated..

Thanks!

The question is "why do you have multiple tables with the same structure"?
You can stick them together in a union query like:

Select "First" as TableName, Field1, Field2, Field3, Field4,...
FROM tblFirst
UNION ALL
Select "Second", Field1, Field2, Field3, Field4,...
FROM tblSecond
UNION ALL
Select "Third", Field1, Field2, Field3, Field4,...
FROM tblThird;

You can then run a query the sets a criteria by the derived TableName field.

 

[Michel Walsh]

Hi,


The solution IS to use UNION query. I don't understand why you say you
cannot use UNION query because the table name varies... that is exactly the
purpose of the UNION query, to bring all the "tables" under ONE name, and
use the WHERE clause, after, to select from which of the table you desire
the data.


Otherwise, you cannot build a static query with a variable table or field
name, in any SQL dialect. SQL is not procedural (you don't say the procedure
to get the result) but declarative (you say what you want, and let the
engine decide the "best" procedure). In SQL, it is like writing many
procedure to get the result, like: if the table is small then do this,
else if the table has index on field1 then do that, else .... and so on.
As you can see, that is generally preferable to write just one procedure, as
you do usually. But to make the PLAN of execution, SQL has to know the
table it works, what field it has to reach, and so on. So, that is why the
table cannot be a "variable", if your query (plan of execution) is to be
"precompiled"


You can build a SQL statement in a string, and execute it (or more likely,
here, to open a recordset on it), at runtime.

Dim str As String
str="SELECT whatever FROM " & tableNameYouWantAndThatYouKnowOnlyAtRunTime &
" WHERE somethingOccured"
Me.ListBox.RowSource = str ' to display the result into a list box, as
example




Hoping it may help,
Vanderghast, Access MVP