Delete record if a specific value is the first of the day

[Guest]

Hello. I need to build a querie that delete a record if one of this values is
the first of the day: "Saída torniquete 1" or "Saída torniquete 2" or "Saída
viaturas".

Imagine that I have this
---------------------------------------------------------------------------------
Employe No. Date Description
1010 15-05-2007 OUT
1010 15-05-2007 IN
1010 15-05-2007 Out

I need to have this
---------------------------------------------------------------------------------
Employe No. Date Description
1010 15-05-2007 IN
1010 15-05-2007 Out


Regards in advance,
Marco

 

[Chris2]

Hello. I need to build a querie that delete a record if one of this values is
the first of the day: "Saída torniquete 1" or "Saída torniquete 2" or "Saída
viaturas".

Imagine that I have this:
-------------------------------------------------------------------- -------------
Employe No. Date Description
1010 15-05-2007 OUT
1010 15-05-2007 IN
1010 15-05-2007 Out

I need to have this:
-------------------------------------------------------------------- -------------
Employe No. Date Description
1010 15-05-2007 IN
1010 15-05-2007 Out


Regards in advance,
Marco

Marco,

So, you want the first occurrence of the value "IN" for column
Description for each employee? (I am assuming this is for time/pay
punching.)

If your [Date] column does not have anything other than the default
(all zeroes) for the individual time of the punch, then there is no
way to distinguish between them. Multiple "IN" values could not be
identified in this case. You would need to redesign your database if
this were so. If you cannot do that, I am not sure of what to do.

On the other hand, if a time value for each punch was also entered for
each [Date] value, then it would work.

I am also assuming that your Date column is of the DATETIME datatype.
If the [Date] column's datatype is CHAR or TEXT, then I would also
recommend database redesign.

Assuming that my assumptions are correct (this is untested):

SELECT EP1.[Employee No.]
,EP1.[Date]
,EP1.Description
FROM EmployeePunches AS EP1
WHERE EP1.Description = "IN"
AND EP1.[Date] =
(SELECT MIN(EP2.[Date])
FROM EmployeePunches AS EP2
WHERE EP2.[Employee No.] = EP1.[Employee No.])


Sincerely,

Chris O.