Query with several tables.

  • Thread starter Thread starter Boon
  • Start date Start date
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
 
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.
 
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 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;
 
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,
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top