Why won't this work? Union Query (Only display active records [check box not selected])

[Kaloyanides]

I can't seem to limit this query to only active records. Active = ckInactive NOT selected in tblEmployees.



SELECT tblEmployees.EmployeeID, UCASE([UserName]) AS FullName, 1 As SortColumn, tblEmployees.ckinactive FROM tblEmployees UNION SELECT 0,"<NEW EMPLOYEE>",0,0 FROM tblEmployees
WHERE tblEmployees.ckinactive = false
ORDER BY SortColumn, fullname;

Thanks!

[Welshgasman]

You need the WHERE on both queries.

[ranman256]

i usu build all my queries, then:
select * from query1
union
select * from query2

[CJ_London]

second query doesn't require a where clause, but the first one does. Try

SELECT tblEmployees.EmployeeID, UCASE([UserName]) AS FullName, 1 As SortColumn, tblEmployees.ckinactive 
FROM tblEmployees
WHERE tblEmployees.ckinactive = false
UNION SELECT 0,"<NEW EMPLOYEE>",0,0 FROM tblEmployees
ORDER BY SortColumn, fullname;

alternatively try

SELECT * FROM
(SELECT tblEmployees.EmployeeID, UCASE([UserName]) AS FullName, 1 As SortColumn, tblEmployees.ckinactive 
FROM tblEmployees
UNION SELECT 0,"<NEW EMPLOYEE>",0,0 FROM tblEmployees) Q
WHERE Q.ckinactive = false
ORDER BY SortColumn, fullname;