Union Query-Multiple Parameters-Table Name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tina - 25 Apr 2008 19:20 GMT

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

Reply to this Message

KARL DEWEY - 25 Apr 2008 20:28 GMT

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?

Signature

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
[quoted text clipped - 3 lines]
> Tina
> KSFC

Reply to this Message

Tina - 25 Apr 2008 20:36 GMT

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.

Signature

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
[quoted text clipped - 9 lines]
> > Tina
> > KSFC

Reply to this Message

KARL DEWEY - 25 Apr 2008 20:55 GMT

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.

Signature

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:
[quoted text clipped - 29 lines]
> > > Tina
> > > KSFC

Reply to this Message

Tina - 25 Apr 2008 20:57 GMT

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

Signature

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
[quoted text clipped - 33 lines]
> > > > Tina
> > > > KSFC

Reply to this Message

John W. Vinson - 26 Apr 2008 02:24 GMT

>Well since I've posted this, I've played some more and finally got this to
>work:
[quoted text clipped - 10 lines]
>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!

Signature

John W. Vinson [MVP]

Reply to this Message