Re: Challenging queries query...


You can do this is two queries.
--First query gets everyone that has a record for the date (save query)
--Second query is an unmatched query that uses the first query and the employees table.

Or you can try the following query


SELECT E.EmpID
FROM Employees as E
LEFT JOIN
(SELECT W.EmpID
FROM WorkHours as W
WHERE W.WorkDate = CDate([Insert Date])) as W2
On E.EmpID = W2.EmpID
WHERE W2.EmpId is Null

scubadiver wrote:

Sorry,

I re-read my message and it had a complete lack of clarity.

The two tables have a 1-to-many relationship so each employeeID has many
weekIDs.

My logic tells me that it isn't possible to list the employees who have a
certain date missing.

"Jerry Whittle" wrote:

PARAMETERS [Enter Date] DateTime;
SELECT Employees.EMPID, Employees.HireDate
FROM Employees
WHERE (((Employees.EMPID) Not In (SELECT Employees.EMPID
FROM Employees
WHERE Employees.HireDate=[Enter Date])));

Something like the above will work IF (1) your date field is a date/time
datatype and (2) it only includes the Date portion and no time (except
midnight). It will take a little more work if you have something like
6/28/2006 11:30AM stored.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"scubadiver" wrote:

Or not as the case may be.

My db holds on-going weekly working hours records for each employee.

I would like to set up a query that will tell me which employees are missing
a particular date (the unmatched query only seems to list those employees
that have NO corresponding weekly records at all).

cheers

.

Relevant Pages

  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: OT: SQL
    ... said was a totally failed attempt to formalize and automate database ... query processing. ... create table Employees ... Manager and Employee Salaries. ...
    (sci.logic)
  • Re: Need query to find employees who have not had required trainin
    ... The first unmatched query works great. ... I think that I might not be building the ALL employees at once, ... You can build a query of all courses taken an employee, ...
    (microsoft.public.access.queries)
  • Re: query a number stored as text
    ... Your query is quite complex, ... I need to join the Employees table to the Orders table like so ... ... "Lee" wrote in message ... >> GlobalSign digital certificate is a forgery and should be deleted without ...
    (microsoft.public.access.queries)
  • Re: Multi-Value Field Query
    ... It was directed at what Microsoft calls a Multi-Valued field, which is not the same as what you have ... This doesn't work very well in the query interface in Access, so you probably need to write a VBA function that does this for you. ... three areas 'CreatedBy', 'Owner', 'Employees'. ... appropriately and it lists all employees involved ...
    (microsoft.public.access.queries)