Filtering in Union Query (Access 2000)

Topic

New Reply

Hi,
Can anyone point out to me why I am getting a parameter prompting for RelType when I run this Union Query? I’m obviously violating something of am not aware of some limitations regarding Union Queries!

The SQL is:
SELECT Customers.[First Name] AS FN, Customers.[Last Name] AS LN, Customers.Title AS TL, Customers.[Work Phone] AS PH, “C” AS RelType
FROM Customers
UNION SELECT Employees.FirstName, Employees.LastName, Employees.Title, Employees.HomePhone, “E”
FROM Employees
WHERE RelType = “E”
ORDER BY TL, LN;

Thanks

Reply | Quote

Replies

You’re trying to define RelType and filter on it at the same time. If you only want employees, not customers, you don’t need a union query anyway.

Reply | Quote

>>> If you only want employees, not customers, you don’t need a union query anyway.
This is true!
Thanx

However (just out of interest!), is it possible by changing the SQL to filter only for Emp’s, or must I create a second query?

Reply | Quote

You’d have to create a second query based on the union query.

Note: there is a discrepancy between the SQL in your post and the one in the screenshot. One mentions “E” and the other “Emp”.

Reply | Quote

OK!

Yes, I changed the SQL slightly just before posting it, and the screenshot is of the original SQL. Thx, I am aware of that!

Reply | Quote

The problem is unrelated to the fact that you’re using a union query – any statement along the lines of SELECT A, B, C AS D FROM XYZ WHERE D = “” will result in the user being prompted for a value for “D”. The reason is that D doesn’t exist as a field in source tables to the query itself, it’s merely a label applied to a field or expression on execution. In the same way, you can’t use GROUP BY D either, you have to GROUP BY whatever it was you’d renamed as “D”.

As any WHERE, GROUP BY etc clause operates on the source data before the query begins its output, these labels don’t really exist at that point.

What you can do, of course, is something like SELECT “A” AS C, “B” AS D, C & D AS E FROM XYZ, because the expression is then working on the output values, not trying to apply it to the input values.

Reply | Quote

Thanx Simon…you have put it very clearly!
It now makes total sense!

See ya around!

Reply | Quote