inverting criteria for query

  • Thread starter Thread starter bicyclops
  • Start date Start date
B

bicyclops

I have a query that selects all employees who have punched-in by selecting
criteria for the column ClockOutTime as (Is Null). This was easy.

Now I'm trying to create criteria that would select all the other employees
in the Employee table. Basically it would mean anyone whose most recent entry
in the table included both a ClockInTime and ClockOutTime.

I'm having trouble figuring out what criteria this would be.

Thanks in advance.
 
I have a query that selects all employees who have punched-in by selecting
criteria for the column ClockOutTime as (Is Null). This was easy.

Now I'm trying to create criteria that would select all the other employees
in the Employee table. Basically it would mean anyone whose most recent entry
in the table included both a ClockInTime and ClockOutTime.

I'm having trouble figuring out what criteria this would be.

Thanks in advance.
Click to expand...

I think you have your criteria backward.
Perhaps you just miss-wrote it here in your message.

ClockOutTime Is Null
should fine all the employees who HAVE NOT punched out (not in).

ClockInTime Is Null
should find employees who HAVE NOT punched in.

ClockOutTime Is Null
should find all employees who have NOT punched out.

ClockInTime Is Not Null
should find all employees who HAVE punched in.

ClockOutTime Is Not Null
should find all employees who HAVE punched out.

ClockInTime Is Not Null AND ClockOutTime Is Not Null
should find all employees who have punched in and punched out.

I'm getting punchy!:-)
 
Fred- Thanks; I probably wrote something backwards.

Regarding the criteria, it's easy to bring up a big list of records that
include where ClockInTime and ClockOutTime are both Not Null.

But I'm finding it harder to produce results to confirm that all the
employees most recent records (not older records) contain both In and Out
times.
 
Fred- Thanks; I probably wrote something backwards.

Regarding the criteria, it's easy to bring up a big list of records that
include where ClockInTime and ClockOutTime are both Not Null.

But I'm finding it harder to produce results to confirm that all the
employees most recent records (not older records) contain both In and Out
times.
Click to expand...

How does Access know which records are the 'most' recent?
Is one of the fields in the record a Date field?

Please copy and paste into a reply message the exact current query SQL
you are using (that works, except for the latest records).
If the field name is not self evident as a Date field, i.e. SalesDate,
indicate which field is the date datatype field.
 
Yes; I'm searching for the most recent date/time each employee has clocked
out. I have been experimenting with domain aggragate functions in my query
but (1) they're slow and (2) I can't seem to sort or filter on the
ClkOutResult field when it contains a Dfirst statement.

Here's the sql. Please keep in mind that I'm not very fluent in sql & have
been using the query builder.

SELECT TblEmployees.EmployeeNumber, TblEmployees.Name, TblEmployees.Current,
nz(DMax("[RecordID]","TblClockIn","[EmployeeID] =" & [EmployeeNumber]),"0")
AS ClkInRecordID, Format(DFirst("[ClockOut]","TblClockIn","[RecordID] =" &
[ClkInRecordID]),"Long Date") AS ClkOutResult
FROM TblEmployees
WHERE (((TblEmployees.Current)=True));
 
Fred- Thanks; I probably wrote something backwards.

Regarding the criteria, it's easy to bring up a big list of records that
include where ClockInTime and ClockOutTime are both Not Null.

But I'm finding it harder to produce results to confirm that all the
employees most recent records (not older records) contain both In and Out
times.
Click to expand...


Something like

Alert: IIf(Not IsNull([ClockInTime]) And _
IsNull([ClockOutTime]),"Not Clocked Out!")

?
 
If you want the last record for each employee and RecordID is an
ever-increasing value then.

SELECT TblEmployees.*
FROM tblEmployees
WHERE tblEmployee.RecordID IN (
SELECT Max(RecordID) as MaxRecord
FROM tblEmployees
GROUP BY EmployeeNumber)
AND (ClockinTime is Null
OR ClockoutTime Is Null)

This should return the last record for each employee where clockIntime
is null or clockouttime is null.

If RecordID is not ever increasing, you will have to write a more
complex query based on the employees latest (Max) clockintime.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Yes; I'm searching for the most recent date/time each employee has clocked
out. I have been experimenting with domain aggragate functions in my query
but (1) they're slow and (2) I can't seem to sort or filter on the
ClkOutResult field when it contains a Dfirst statement.

Here's the sql. Please keep in mind that I'm not very fluent in sql & have
been using the query builder.

SELECT TblEmployees.EmployeeNumber, TblEmployees.Name, TblEmployees.Current,
nz(DMax("[RecordID]","TblClockIn","[EmployeeID] =" & [EmployeeNumber]),"0")
AS ClkInRecordID, Format(DFirst("[ClockOut]","TblClockIn","[RecordID] =" &
[ClkInRecordID]),"Long Date") AS ClkOutResult
FROM TblEmployees
WHERE (((TblEmployees.Current)=True));




fredg said:
How does Access know which records are the 'most' recent?
Is one of the fields in the record a Date field?

Please copy and paste into a reply message the exact current query SQL
you are using (that works, except for the latest records).
If the field name is not self evident as a Date field, i.e. SalesDate,
indicate which field is the date datatype field.
Click to expand...
Click to expand...
 
Back
Top