Query to show which employee is not listed in another query

[smeghead67]

Hello,

I am working on a secret shopper database for the employees in my department.

I have employee info eg. employee id, first, last names in table emp. I have the fields named as emp.empid, emp.first emp.last



I have the shopping report info eg. employee id, date and time in table shop. I have the fields named as shop.empid shop.dt The employee id field [shop].[empid] is in a relationship with the [emp].[empid] field

my basic query to look at the info is [getshop]

SELECT emp.last, emp.first, shop.empid, shop.dt
FROM emp INNER JOIN shop ON emp.empid=shop.empid;

now to get a count of how many times a person has been shopped i run the [shopped] query

SELECT DISTINCTROW getshop.empid, getshop.first, getshop.last, Count(*) AS TS
FROM getshop
GROUP BY getshop.empid, getshop.first, getshop.last;

This gives me a list of people who have been shopped and how many times they have been shopped.

now, I want a query called [notshopped] that will give me a list of people who have not been shopped yet.

The best i have came up with is

SELECT emp.empid, emp.first, emp.last
From emp
Where [emp].[empid] <> [getshop].[empid]

But it asks me for the value of [getshop].[empid]

Could the wonderful coders of AccessForums.net point me in the correct direction?

Thank you
Ted

[pbaldy]

Try the unmatched query wizard to compare the query of shopped against the table of all.

[smeghead67]

Thank you Paul,

I didn't think to check the wizard as I tend to write all my queries by hand. The wizard gave me the following code.

SELECT emp.last, emp.first
FROM emp LEFT JOIN shopped ON emp.[empid] = shopped.[empid]
WHERE (((shopped.empid) Is Null));

I can work from there, thank you again.

Ted

[pbaldy]

No problem Ted.