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]
Code:
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
Code:
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
Code:
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