Hiding second set of records

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I'm using a crosstab query to keep track of employees who are tardy on a
daily basis. The times are tracked by the employee undocking and re-docking
his handheld computer from the docking station. At times the employee needs
to re-dock and pull the computer out again creating too logs of "out" times
in the table. I need to be able to capture the first record instead of the
second record.

My table is setup as follows

TblID ID Time Status Date
#### 009 6:58 a.m. OUT 06/14/08
#### 009 7:10 a.m. OUT 06/14/08
#### 009 3:32 p.m. IN 06/14/08

My crosstab query is setup to only show records that are from 7:01 a.m. and
up with a status of "OUT".

I need to have the second record ignored so that the employee doesn't get
charged for a tardy. Any help will be greatly appreciated.
 
Dennis said:
I'm using a crosstab query to keep track of employees who are tardy
on a daily basis. The times are tracked by the employee undocking
and re-docking his handheld computer from the docking station. At
times the employee needs to re-dock and pull the computer out again
creating too logs of "out" times in the table. I need to be able to
capture the first record instead of the second record.

My table is setup as follows

TblID ID Time Status Date
#### 009 6:58 a.m. OUT 06/14/08
#### 009 7:10 a.m. OUT 06/14/08
#### 009 3:32 p.m. IN 06/14/08

My crosstab query is setup to only show records that are from 7:01
a.m. and up with a status of "OUT".

I need to have the second record ignored so that the employee doesn't
get charged for a tardy. Any help will be greatly appreciated.
Click to expand...

Use a grouping query as the source of the crosstab - group by ID, status and
Date and use Min([Time]) to get the earlier time.

Incidently, you might want to consider using more descriptive field names:
"Date" and "Time" are both reserved keywords and should be avoided when
naming objects (http://www.aspfaq.com/show.asp?id=2080). How about
"EmployeeID", "DockDate", "DockStatus" and "DockTime"?
 
Back
Top