A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Time and Attendance System Query



 
 
Thread Tools Display Modes
  #1  
Old March 3rd, 2006, 05:44 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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
  #2  
Old March 5th, 2006, 01:54 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Time and Attendance System Query

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.

  #3  
Old March 7th, 2006, 10:01 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Time and Attendance System Query

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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Please help me fix this flawed query formula circuit slave Running & Setting Up Queries 7 February 7th, 2006 04:44 AM
time and attendance JGB Worksheet Functions 0 January 25th, 2006 06:20 PM
Adding Multiple rows at a time Slider Using Forms 9 January 24th, 2006 02:33 PM
Want current time to be entered w/double click hiteckchick21 General Discussion 3 May 25th, 2005 04:40 PM
Query in a query PPerry Running & Setting Up Queries 1 June 2nd, 2004 05:53 PM


All times are GMT +1. The time now is 12:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.