TIME LOG CALCULATIONS

[BOMBEROSAGS]

I think I may be over my head, but best ask.

I need to do the following. import an excell sheet of time logs and have my
database automatically calculate the Employee ID , Time IN and OUT and
DURATION, into one database for further cunsultation. I know this can be
done, just really stuck and an afraid that I will have to use scripting,
something I do not know a lot about.

below is a sample of my raw data;

Date Time ID Index Name Action Cmd Status Data1 Data2 Data3 Port Read
2/21/2009 15:12:23 8 0 Verify ID Success 2 90 66 Keypad
2/21/2009 15:11:27 8 0 Verify ID Success 2 103 66 Keypad
2/21/2009 15:11:08 0 0 Erase Transaction Log Success 0 0 0 Ethernet

 

[Allen Browne]

If I understand, you want to assume that the first row for a particular ID
is an IN, and the next row (based on the date/time) is an OUT. Your question
is how you can calculate the time difference between the IN record and the
OUT record.

The basic idea is to use a subquery to get the time from the next matching
record. For an introduction to subqueries, see:
http://allenbrowne.com/subquery-01.html#AnotherRecord

In your case there's a bit more to it than just that. You will need to pin
down which is the OUT record matching each IN, and limit your query to that.
Where this gets complicated is the possibility of non-matching records, e.g.
the person forgot to sign in (or out.)

 

[BOMBEROSAGS]

Anther item I might include in this is that not necessarly the employee in
and out stamp will be together, unless I arrange it in the excell worksheet
first.

I am workingwith the biometric divice to include a code to In or OUT, but at
this point no luck.

 

[Allen Browne]

The approaches suggested don't assume the 2 records are together.

At some point, I think you will have to have some human intervention to
decide which are INs and which are OUTs - at least for the odd cases.