Query with several tables.


B

Boon

Hi,

I have tables TableA, TableB, TableC. All 3 have the same format, same
field names. Just different data. Think of it as 3 different results of
simulation runs.

I have query that calculate the summary of this table. One at a time. I wnat
it to be possible that when I click the query, I can put in the table name,
and then the query will calculate from that table.

Thank you,
B
 
Ad

Advertisements

K

KARL DEWEY

You could have a form with an option group or check boxes to select which
table.

Then use a union query that includes criteria from the form to control which
query of the union outputs data.
 
B

Boon

Agreed. The form looks to be a best way, but I want to do it in a query if
possible. A query will be more flexible for me.

thanks,
 
K

KARL DEWEY

You could have a prompt like this --
[1- TableA 2- TableB 3- TableC]

Then the query like this --
SELECT Field1, Field2, [1- TableA 2- TableB 3- TableC] AS [ParmInput]
FROM TableA
WHERE [1- TableA 2- TableB 3- TableC] = 1
UNIOM ALL SELECT Field1, Field2, [1- TableA 2- TableB 3- TableC] AS
[ParmInput]
FROM TableB
WHERE [1- TableA 2- TableB 3- TableC] = 2
UNIOM ALL SELECT Field1, Field2, [1- TableA 2- TableB 3- TableC] AS
[ParmInput]
FROM TableC
WHERE [1- TableA 2- TableB 3- TableC] = 3;
 
B

Boon

Thanks. But you assume that there are only 3 tables and the name is fix.

I was thinking of using a prompt too but found out I cannot use table name
as a parameter.

Looks like I need to create a form...



KARL DEWEY said:
You could have a prompt like this --
[1- TableA 2- TableB 3- TableC]

Then the query like this --
SELECT Field1, Field2, [1- TableA 2- TableB 3- TableC] AS [ParmInput]
FROM TableA
WHERE [1- TableA 2- TableB 3- TableC] = 1
UNIOM ALL SELECT Field1, Field2, [1- TableA 2- TableB 3- TableC] AS
[ParmInput]
FROM TableB
WHERE [1- TableA 2- TableB 3- TableC] = 2
UNIOM ALL SELECT Field1, Field2, [1- TableA 2- TableB 3- TableC] AS
[ParmInput]
FROM TableC
WHERE [1- TableA 2- TableB 3- TableC] = 3;

--
Build a little, test a little.


Boon said:
Agreed. The form looks to be a best way, but I want to do it in a query
if
possible. A query will be more flexible for me.

thanks,
 
Ad

Advertisements

K

KARL DEWEY

But you assume that there are only 3 tables and the name is fix.
So build it with more.
What I posted does not use table name as parameter.
You can use a two column combo and the query but pull criteria from combo
instead of prompt.
KARL DEWEY said:
You could have a prompt like this --
[1- TableA 2- TableB 3- TableC]

Then the query like this --
SELECT Field1, Field2, [1- TableA 2- TableB 3- TableC] AS [ParmInput]
FROM TableA
WHERE [1- TableA 2- TableB 3- TableC] = 1
UNIOM ALL SELECT Field1, Field2, [1- TableA 2- TableB 3- TableC] AS
[ParmInput]
FROM TableB
WHERE [1- TableA 2- TableB 3- TableC] = 2
UNIOM ALL SELECT Field1, Field2, [1- TableA 2- TableB 3- TableC] AS
[ParmInput]
FROM TableC
WHERE [1- TableA 2- TableB 3- TableC] = 3;

--
Build a little, test a little.


Boon said:
Agreed. The form looks to be a best way, but I want to do it in a query
if
possible. A query will be more flexible for me.

thanks,



You could have a form with an option group or check boxes to select
which
table.

Then use a union query that includes criteria from the form to control
which
query of the union outputs data.

--
Build a little, test a little.


:

Hi,

I have tables TableA, TableB, TableC. All 3 have the same format,
same
field names. Just different data. Think of it as 3 different results
of
simulation runs.

I have query that calculate the summary of this table. One at a time.
I
wnat
it to be possible that when I click the query, I can put in the table
name,
and then the query will calculate from that table.

Thank you,
B
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top