help with record selection

[Guest]

The question I have may have a simpler than I think. I have a form and
subform. The form contains employee info and the subform contains working
hours.

How can I find out which employees don't have entered records for a certain
week as opposed to having no records at all?

Cheers

 

[Allen Browne]

Use a subquery.

Example here:
Subquery basics: Identifying what is NOT there
at:
http://allenbrowne.com/subquery-01.html#NotThere

 

[Guest]

After posting this question I had a go but didn't fully resolve the problem.
Here is the SQL that I have written:

SELECT tble_employee.EmployeeID, tble_employee.Employee
FROM tble_employee INNER JOIN tble_hrs ON tble_employee.EmployeeID =
tble_hrs.EmployeeID
WHERE NOT EXISTS

(SELECT tble_hrs.CustomerID
FROM tble_hrs
WHERE tble_hrs.RecWk = #03/08/2007#);

How right or wrong am I?

Cheers

 

[Guest]

After a brief learning curve:

SELECT tble_employee.EmployeeID, tble_employee.Employee, tble_employee.Dept,
tble_employee.Work, tble_hrs.RecWk
FROM tble_employee INNER JOIN tble_hrs ON tble_employee.EmployeeID =
tble_hrs.EmployeeID
WHERE (((tble_employee.Work)=True) AND ((Exists (SELECT tble_employee.Employee
FROM tble_employee INNER JOIN tble_hrs ON tble_employee.EmployeeID =
tble_hrs.EmployeeID
WHERE (((tble_hrs.RecWk)=#03/8/2007#))))=False));

 

[Allen Browne]

You don't need tbl_hrs in the main query.

In the subquery, you need to matc on the EmployeeID and on the Recwk:

SELECT tble_employee.EmployeeID, tble_employee.Employee
FROM tble_employee
WHERE NOT EXISTS
(SELECT tble_hrs.CustomerID
FROM tble_hrs
WHERE (tble_employee.EmployeeID = tble_hrs.EmployeeID)
AND (tble_hrs.RecWk = #03/08/2007#));

(Note that if you did need to use tbl_hrs in the main query for some reason,
you would need to alias the table so that it has a different name than in
the subquery.)