Challenging queries query...

[scubadiver]

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

 

[Jerry Whittle]

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.

 

[scubadiver]

Thanks for the reply.

My weekID is a date/time type without the time portion.

Will give it a whirl tomorrow

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.

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

 

[scubadiver]

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.

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.

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

 

[John Spencer]

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

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.

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.

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

 

[scubadiver]

So how would this work then?

Is it just a table with one 'date' field? How would this work in a query?

Cheers

: