Date not in list

J

jdbit2byte

Say an input form stores data in these fields in tbl_Time:
EmployeeID
Date
LaborHours
TravelHours

What kind of query would I run on tbl_Time to find out which employees did
not enter an entry for yesterday?

Thanks
JD
 
J

John Spencer

Do you have a table that lists employees?

SELECT Employees.*
FROM Employees
WHERE NOT EXISTS
(SELECT *
FROM tbl_Time
WHERE Tbl_Time.Date = DateAdd("d",-1,Date())
AND tbl_Time.EmployeeID = Employees.EmployeeID)

That is liable to be slow, so you might try this variation which should be
faster.

SELECT Employees.*
FROM Employees LEFT JOIN
(SELECT EmployeeID
FROM tbl_Time
WHERE Tbl_Time.Date = DateAdd("d",-1,Date())) as S
ON Employees.EmployeeID = S.EmployeeID
WHERE S.EmployeeID is Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

jdbit2byte

By passing the queryed data through two
queries the data needed was able to be bound to a report.
The first query calles time entries from tbl_Time
The first query's criteria is:
Day() -1
The secound is a Find Unmatched Query, it calls TechID from the Tech table
and TechID, TechName from the first query.
Criteria for the second query:
TechID (from the first query) IsNull
The query then compares entries to the tech table to find which techs had no
entry yesterday.

The only fault is if it is monday. I would like for the first query to
check:
IF today is Monday THEN return all entries for Friday

Thx for the help,
 
J

John W. Vinson

The only fault is if it is monday. I would like for the first query to
check:
IF today is Monday THEN return all entries for Friday
Click to expand...

What if Monday is a holiday? Would you then want to see Friday's data
if the query is run on Tuesday?

What if Friday was a holiday? Would you want to see the previous
Thursday's results?

See http://www.mvps.org/access/datetime/date0012.htm for some VBA code
to do workday math.

John W. Vinson [MVP]
 
Top