Table selection in Query

[Guest]

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.

 

[Guest]

Hey Rob,

It is possible to build queries and reports on the fly using SQL in VBA,
even your Union Query. But if you are relatively new to coding, this may not
be the route for you.

I suggest taking a look at the following website as a starting point:

http://www.fontstuff.com/access/index.htm

It has been very helpful to me in the past.

 

[Guest]

Hey xRoachx

Thanks for the tip. It did help me in some ways, ie. learned new tricks.
However, I tried following some of the examples and didnt do what I want to
do.

Here's what I did and it didnt work. Maybe you can shed some light as to the
proper way of handling my situation:

I created a form (frmTableNames) with combo box and named it cboTableName
(lookup table is tblTableNames where it has all the table names ie..
tblPSTAIR_0803, tblPSTAIR_0903, etc.). In the form, I choose the table that
I need, let us say, tblPSTAIR_0903 from the drop down list.

The I wrote this query:
Select * from [forms!frmTableNames!cboTableName.value]
where Client like 'XXXX*'

Im getting an error msg: "The Microsoft Jet database engine cannot find the
input table or query ......etc "

What Im trying to do is to be able to create 1 multi-purpose query that I
can append the table name that I need (that are stored in the tblTableNames
table).

Is this possible at all?

Thanks so much for your assistance.

Rob

 

[Guest]

Hey xRoachx

I got my answer. I was playing around the code in VB and got it to work.

Thanks very much again!

Rob

Hey xRoachx

Thanks for the tip. It did help me in some ways, ie. learned new tricks.
However, I tried following some of the examples and didnt do what I want to
do.

Here's what I did and it didnt work. Maybe you can shed some light as to the
proper way of handling my situation:

I created a form (frmTableNames) with combo box and named it cboTableName
(lookup table is tblTableNames where it has all the table names ie..
tblPSTAIR_0803, tblPSTAIR_0903, etc.). In the form, I choose the table that
I need, let us say, tblPSTAIR_0903 from the drop down list.

The I wrote this query:
Select * from [forms!frmTableNames!cboTableName.value]
where Client like 'XXXX*'

Im getting an error msg: "The Microsoft Jet database engine cannot find the
input table or query ......etc "

What Im trying to do is to be able to create 1 multi-purpose query that I
can append the table name that I need (that are stored in the tblTableNames
table).

Is this possible at all?

Thanks so much for your assistance.

Rob

Hey Rob,

It is possible to build queries and reports on the fly using SQL in VBA,
even your Union Query. But if you are relatively new to coding, this may not
be the route for you.

I suggest taking a look at the following website as a starting point:

http://www.fontstuff.com/access/index.htm

It has been very helpful to me in the past.