Union Query Question

  • Thread starter Thread starter grep
  • Start date Start date
G

grep

Given this query:

SELECT CompanyID, CompanyName, Inactive FROM tblCustomers UNION SELECT
VendorID AS CompanyID, CompanyName, Inactive FROM tblVendors
WHERE Inactive=No
ORDER BY CompanyName;

Why do I still get EVERY record, including the ones where Inactive is True?

I've tried substituting 0 and False for No, with the same results.

TIA

grep
 
I think the WHERE Clause only applies to the second part
in you UNION and therefore the first part will return all
Records from tblCustomers.

Try with the WHERE Clause in *both* parts of the union.

BTW, don't use aliases in the second part of the Union
Query. Aliases are ignored in the second part of the
UNION Query.

Use False or zero.

HTH
Van T. Dinh
MVP (Access)
 
Thanks, Van - that was it. Oh, and if you mean the AS part, it works
fine in the second part of the Union.

grep
 
Union Queries always use the Filed names (including aliases) from the first
part of the Union and ignore the names (including aliases) from the second
part. In your case the alias is the same as the Field name of the
corresponding Field of the first part. Hence, it gives you the wrong
impression that the alias in the second part works.
 
Back
Top