Union Query-Multiple Parameters-Table Name

[Tina]

What I'm trying to make is a Union Query that has a field for table names and
lets those table names be a parameter. I have figured out how to do
parameters (where option), and I have figured out how to do a field for table
names ("table name"), but combining the two is beyond me. Any help?

Tina
KSFC

 

[KARL DEWEY]

It sounds like it cannot be done. Explain a little more. Post some SQL that
you think it might look like so I can better understand what you are
attempting to do.

Do you expect to use the field for table names in the FROM part of the SQL?

 

[Tina]

Well since I've posted this, I've played some more and finally got this to
work:

SELECT "Certified Sample Center", [Title], [FC], [PMV Description]
FROM [Certified Sample Center]
WHERE [FC] LIKE [FC Code]
AND [Department]
UNION SELECT "Transfers", [Title], [FC], [PMV Description]
FROM [Transfers]
WHERE [FC] LIKE [FC Code]
AND [Department];

The "department" in the select section creates the new field where the table
name is insterted. My worry is that having the second parameter as just
[Department] that it will look for whatever is inserted there in all fields,
but I only want it to search in that one field w/ the table names. If
nothing more can be done, then this will be good enough. Just want to know
if I can get that specified. I did try putting the label that the column
gets where the table name is inserted (expr1003), but that didn't work.

 

[KARL DEWEY]

Someone else will need to answer your questions as it is beyond me.
I tried a query and did not get any different results with the added part of
the WHERE statement.
--
KARL DEWEY
Build a little - Test a little

Well since I've posted this, I've played some more and finally got this to
work:

SELECT "Certified Sample Center", [Title], [FC], [PMV Description]
FROM [Certified Sample Center]
WHERE [FC] LIKE [FC Code]
AND [Department]
UNION SELECT "Transfers", [Title], [FC], [PMV Description]
FROM [Transfers]
WHERE [FC] LIKE [FC Code]
AND [Department];

The "department" in the select section creates the new field where the table
name is insterted. My worry is that having the second parameter as just
[Department] that it will look for whatever is inserted there in all fields,
but I only want it to search in that one field w/ the table names. If
nothing more can be done, then this will be good enough. Just want to know
if I can get that specified. I did try putting the label that the column
gets where the table name is inserted (expr1003), but that didn't work.
--
Tina
KSFC

It sounds like it cannot be done. Explain a little more. Post some SQL that
you think it might look like so I can better understand what you are
attempting to do.

Do you expect to use the field for table names in the FROM part of the SQL?

 

[Tina]

Thanks anyway. I think what I have is good enough. Our departments aren't
that alike that this would cause problems.
--
Tina
KSFC

Someone else will need to answer your questions as it is beyond me.
I tried a query and did not get any different results with the added part of
the WHERE statement.
--
KARL DEWEY
Build a little - Test a little

Well since I've posted this, I've played some more and finally got this to
work:

SELECT "Certified Sample Center", [Title], [FC], [PMV Description]
FROM [Certified Sample Center]
WHERE [FC] LIKE [FC Code]
AND [Department]
UNION SELECT "Transfers", [Title], [FC], [PMV Description]
FROM [Transfers]
WHERE [FC] LIKE [FC Code]
AND [Department];

The "department" in the select section creates the new field where the table
name is insterted. My worry is that having the second parameter as just
[Department] that it will look for whatever is inserted there in all fields,
but I only want it to search in that one field w/ the table names. If
nothing more can be done, then this will be good enough. Just want to know
if I can get that specified. I did try putting the label that the column
gets where the table name is inserted (expr1003), but that didn't work.
--
Tina
KSFC

It sounds like it cannot be done. Explain a little more. Post some SQL that
you think it might look like so I can better understand what you are
attempting to do.

Do you expect to use the field for table names in the FROM part of the SQL?
--
KARL DEWEY
Build a little - Test a little


:

What I'm trying to make is a Union Query that has a field for table names and
lets those table names be a parameter. I have figured out how to do
parameters (where option), and I have figured out how to do a field for table
names ("table name"), but combining the two is beyond me. Any help?

Tina
KSFC

 

[John W. Vinson]

Well since I've posted this, I've played some more and finally got this to
work:

SELECT "Certified Sample Center", [Title], [FC], [PMV Description]
FROM [Certified Sample Center]
WHERE [FC] LIKE [FC Code]
AND [Department]
UNION SELECT "Transfers", [Title], [FC], [PMV Description]
FROM [Transfers]
WHERE [FC] LIKE [FC Code]
AND [Department];

The "department" in the select section creates the new field where the table
name is insterted.

Ummm... no, it doesn't. The "Certified Sample Center" and "Transfers" do that.

A criterion of

AND [Department]

will cause the SELECT clause to return a record if the table field
[Department] - or, if there is no such field, the response when the user is
prompted with

Department

in a popup box - is not equal to zero; if Department is 0 or null, the record
will not be retrieved. This may be working for you but likely for the wrong
reasons!