Time and Attendance System Query

I am attempting to query a time and attendance database (Attendance
Enterprise by Infotronics) to determine the hours worked for any range of
time periods, classes of employees, shifts, etc.

While I am not entirely familiar with this database, it appears that in
order to get the most recent information, I need to use the "Transactions"
table, which has the following fields:
UNIQUEID
FILEKEY (identifies the employee)
DTTMSTAMP (date time stamp)
TRANSTYPE (actual punch or manual correction)
STATION (identifies the facility)
PERIOD (not populated)

A record is written every time an employee punches.

It appears that the only way to determine hours worked is to calculate time
elapsed between records.

In order to get total hours worked for a day (a day being defined as 7:00 am
to 7:00 am), here are the steps that I have come up with:
1) Pull a subset of data for the time period
2) Sort by FILEKEY, then DTTMSTAMP
3) Establish a new field called EMPTRANSNUM, which would number each
transaction for each employee
4) Make the assumption that the first transaction is an "in" punch.
5) Calculate time elapsed between transactions for each employee. For
example, a normal employee would have 4 transactions, "in" for the day, "out"
for lunch, "in" from lunch, and "out" for the day. So, the calculation for
this employee would be ((2-1) - (3-2) + (4-3))*24.

It is understood that missed punches make complete accuracy impossible.

My first question is, how do I populate the EMPTRANSNUM in step 3 above?

My second question is, am I missing something that would make this process
simpler?

Thanks.
--
Dan Young

Your proposed field [EMPTRANSNUM] can easily be calculated (and
therefore, IMHO, should definitely not be a stored field in a Table, but
rather a calculated field in a Query). However, I think you have worse
problems, and adding an [EMPTRANSNUM] field will not help you at all in
solving them.

Specifically, I do not perceive that you have any means to automatically
determine the direction ("in" vs. "out") of any of these transactions.
You might guess, perhaps based on time of day, which type it is, but you
have legal requirements to satisfy, and people's money is dependent on
what you do, so just guessing probably isn't a very responsible way to
proceed.

My suggestion would be to add a suitable field to the time records to
specify "in"/"out", and have that populated manually. For example, do
you now keep paper records of these transactions (e.g., on physical time
cards that an employee must sign to certify their validity)? Looking at
the place where each time stamp occurs could identify the type. Or the
employee could underline or circle each time he punches in (and no others).

I'm not familiar with "Attendance Enterprise", but perhaps the field I'm
describing is already present in the database, but in another Table.
Such other Table could be linked to [Transactions] via the [FILEKEY] and
[DTTMSTAMP] fields. To me, it doesn't seem unreasonable to calculate
time worked by subtracting time in from time out; this is probably a
pretty common practice, even though it doesn't cover coffee breaks or
daydreams, and it's probably sufficient to satisfy the employer's legal
obligations.

I didn't understand your expression "((2-1) - (3-2) + (4-3))*24". Does
the first "2" refer to a date/time value corresponding to when the
employee stopped working in order to eat lunch? Or does "(2-1)" refer
to that date/time value? The "*24" at the end suggests that all of
these values are measured in days, rather than hours, which by itself
indicates a rather unusual style of time clock.

What you might do, if the transaction direction ("in"/"out", not the
[TRANSTYPE] field) really is not automatically available, and this
actually seems a bit difficult to believe of a commercial system, is to
have someone manually edit all the records to ensure that the direction
is correctly noted. It would take some work, but wouldn't be a
monumental task. Then, having sequestered the records for anyone
working over a weekend, and having made corrections where, for example,
an employee had two successive "in" records, you could start at the
beginning of the week and subtract each "in" record from the next
following "out" record for each employee, stopping at the end of the
week and calculating the total. If some employee did not have
alternating "in" and "out" records for the week, you'd need to examine
the paper records to determine what went wrong (and try to correct it),
but otherwise I think you'd have just what you need.

-- Vincent Johns
Please feel free to quote anything I say here.


Dan Young wrote:

I am attempting to query a time and attendance database (Attendance
Enterprise by Infotronics) to determine the hours worked for any range of
time periods, classes of employees, shifts, etc.

While I am not entirely familiar with this database, it appears that in
order to get the most recent information, I need to use the "Transactions"
table, which has the following fields:
UNIQUEID
FILEKEY (identifies the employee)
DTTMSTAMP (date time stamp)
TRANSTYPE (actual punch or manual correction)
STATION (identifies the facility)
PERIOD (not populated)

A record is written every time an employee punches.

It appears that the only way to determine hours worked is to calculate time
elapsed between records.

In order to get total hours worked for a day (a day being defined as 7:00 am
to 7:00 am), here are the steps that I have come up with:
1) Pull a subset of data for the time period
2) Sort by FILEKEY, then DTTMSTAMP
3) Establish a new field called EMPTRANSNUM, which would number each
transaction for each employee
4) Make the assumption that the first transaction is an "in" punch.
5) Calculate time elapsed between transactions for each employee. For
example, a normal employee would have 4 transactions, "in" for the day, "out"
for lunch, "in" from lunch, and "out" for the day. So, the calculation for
this employee would be ((2-1) - (3-2) + (4-3))*24.

It is understood that missed punches make complete accuracy impossible.

My first question is, how do I populate the EMPTRANSNUM in step 3 above?

My second question is, am I missing something that would make this process
simpler?

Thanks.

After consideration of your input, I have decided to seek help from the
vendor to come up with a means of determining hours worked that is more
definite.

I sincerely appreciate your help...
--
Dan Young


"Vincent Johns" wrote:

Your proposed field [EMPTRANSNUM] can easily be calculated (and
therefore, IMHO, should definitely not be a stored field in a Table, but
rather a calculated field in a Query). However, I think you have worse
problems, and adding an [EMPTRANSNUM] field will not help you at all in
solving them.

Specifically, I do not perceive that you have any means to automatically
determine the direction ("in" vs. "out") of any of these transactions.
You might guess, perhaps based on time of day, which type it is, but you
have legal requirements to satisfy, and people's money is dependent on
what you do, so just guessing probably isn't a very responsible way to
proceed.

My suggestion would be to add a suitable field to the time records to
specify "in"/"out", and have that populated manually. For example, do
you now keep paper records of these transactions (e.g., on physical time
cards that an employee must sign to certify their validity)? Looking at
the place where each time stamp occurs could identify the type. Or the
employee could underline or circle each time he punches in (and no others).

I'm not familiar with "Attendance Enterprise", but perhaps the field I'm
describing is already present in the database, but in another Table.
Such other Table could be linked to [Transactions] via the [FILEKEY] and
[DTTMSTAMP] fields. To me, it doesn't seem unreasonable to calculate
time worked by subtracting time in from time out; this is probably a
pretty common practice, even though it doesn't cover coffee breaks or
daydreams, and it's probably sufficient to satisfy the employer's legal
obligations.

I didn't understand your expression "((2-1) - (3-2) + (4-3))*24". Does
the first "2" refer to a date/time value corresponding to when the
employee stopped working in order to eat lunch? Or does "(2-1)" refer
to that date/time value? The "*24" at the end suggests that all of
these values are measured in days, rather than hours, which by itself
indicates a rather unusual style of time clock.

What you might do, if the transaction direction ("in"/"out", not the
[TRANSTYPE] field) really is not automatically available, and this
actually seems a bit difficult to believe of a commercial system, is to
have someone manually edit all the records to ensure that the direction
is correctly noted. It would take some work, but wouldn't be a
monumental task. Then, having sequestered the records for anyone
working over a weekend, and having made corrections where, for example,
an employee had two successive "in" records, you could start at the
beginning of the week and subtract each "in" record from the next
following "out" record for each employee, stopping at the end of the
week and calculating the total. If some employee did not have
alternating "in" and "out" records for the week, you'd need to examine
the paper records to determine what went wrong (and try to correct it),
but otherwise I think you'd have just what you need.

-- Vincent Johns
Please feel free to quote anything I say here.


Dan Young wrote:

I am attempting to query a time and attendance database (Attendance
Enterprise by Infotronics) to determine the hours worked for any range of
time periods, classes of employees, shifts, etc.

While I am not entirely familiar with this database, it appears that in
order to get the most recent information, I need to use the "Transactions"
table, which has the following fields:
UNIQUEID
FILEKEY (identifies the employee)
DTTMSTAMP (date time stamp)
TRANSTYPE (actual punch or manual correction)
STATION (identifies the facility)
PERIOD (not populated)

A record is written every time an employee punches.

It appears that the only way to determine hours worked is to calculate time
elapsed between records.

In order to get total hours worked for a day (a day being defined as 7:00 am
to 7:00 am), here are the steps that I have come up with:
1) Pull a subset of data for the time period
2) Sort by FILEKEY, then DTTMSTAMP
3) Establish a new field called EMPTRANSNUM, which would number each
transaction for each employee
4) Make the assumption that the first transaction is an "in" punch.
5) Calculate time elapsed between transactions for each employee. For
example, a normal employee would have 4 transactions, "in" for the day, "out"
for lunch, "in" from lunch, and "out" for the day. So, the calculation for
this employee would be ((2-1) - (3-2) + (4-3))*24.

It is understood that missed punches make complete accuracy impossible.

My first question is, how do I populate the EMPTRANSNUM in step 3 above?

My second question is, am I missing something that would make this process
simpler?

Thanks.