Possible to return NON duplicates fields?

[Rocky]

I can't seem to wrap my mind around this one...
and many others for that matter =;-)

I know how to find duplicate records but I'm stumped on how to return all
the records which are NOT duplicates.

Here's the real world senario. We have a table with current employees
[tbl_CurrentEmp]. Another one is a list of employees [tbl_MixedEmp] from one
of our systems. Because of licensing issues we need to find all the employees
in tbl_MixedEmp which are no longer active. Thanks if you can point me in the
right direction.

 

[KARL DEWEY]

Seems to me this is a case for 'Unmatched' query.
SELECT Employee
FROM [tbl_MixedEmp] LEFT JOIN [tbl_CurrentEmp] ON [tbl_MixedEmp].Employee
=[tbl_CurrentEmp].Employee
WHERE [tbl_CurrentEmp].Employee Is Null
ORDER BY Employee

For non-duplicate --
SELECT Employee
FROM SomeTable
WHERE Count(Employee) = 1
GROUP BY Employee;

 

[KenSheridan via AccessMonster.com]

The latter should be:

SELECT Employee
FROM SomeTable
GROUP BY Employee
HAVING COUNT(*) = 1;

The WHERE clause addresses the data before grouping, so cannot contain an
aggregation operator. The HAVING clause on the other hand operates after
grouping.

Also, in the first query, as both tables include an Employee column, the
reference to the column in the SELECT clause needs to be qualified with the
table name:

SELECT tbl_MixedEmp.Employee

Ken Sheridan
Stafford, England

Seems to me this is a case for 'Unmatched' query.
SELECT Employee
FROM [tbl_MixedEmp] LEFT JOIN [tbl_CurrentEmp] ON [tbl_MixedEmp].Employee
=[tbl_CurrentEmp].Employee
WHERE [tbl_CurrentEmp].Employee Is Null
ORDER BY Employee

For non-duplicate --
SELECT Employee
FROM SomeTable
WHERE Count(Employee) = 1
GROUP BY Employee;

I can't seem to wrap my mind around this one...
and many others for that matter =;-)

[quoted text clipped - 7 lines]

in tbl_MixedEmp which are no longer active. Thanks if you can point me in the
right direction.