By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Separating data by date

P: n/a
I have a problem with access and our time clocks. We have time clocks
that put out a .log file with the badge swipes for everybody. There is
one .log file for each day. I am pulling that data into an access
database to manipulate it. In the table I have:

FIRSTNAME | LASTNAME | BADGE# | DATE | TIME

The problem is that the time clock doesn’t differentiate between a
“clock in” and a “clock out”. It only records badge swipes. So for
each person, I could have possibly 4-6 badge swipes per day. (clock-in
in the morning, out for lunch, in from lunch, out for the day, and if
someone needs to go to the doctor or something then they would clock out
and back in for that). Each badge swipe creates a new record in the
database. The only thing that changes is the date and time. So that
means that I could have up to 4-6 records per person, per day.
What I would like to do is separate those badge swipes, (database
records), into groups of 2 based on the badge number and then have
access do the math to tell how many hours are in between each clock in
and out. Then I would like to add the totals for all of the groups for
each person together and come up with a total time on the clock for the
day. As you can probably tell, this is for time-keeping/payroll.
I know how to get access to do the math, but I can’t figure out how to
get it to separate the badge swipes into groups of 2. Is there anyone
out there with any ideas?
Thanks in advance.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
First off, what happens when someone forgets to clock out? Doesn't that
cause a ton of problems?

Next, what happens if someone works past midnight? Then there are records in
two different files.

Assuming the above are (somehow) not issues, you could just make a recordset
of the data ordered by person and time. Then you could walk that dataset in
code grabbing the data from two records at a time.

Here are three totally untested functions (well, one plus two shells). Do
note that the cases I mentioned above, and probably some others as well, may
well be significant issues, even if they only come up very rarely. I would
think a lot about what odditites there could be in the data and test the
heck out of your code so that you make sure it takes those situations into
accout. Mine doesn't deal with them at all. And, again, I've not even tried
running this code, so do be careful.

Jeremy

Function CalcTime()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dtmIn As Date
Dim dtmOut As Date
Dim strSql As String
Dim lngBadge As Long
Dim sngHoursWorked As Single

Set db = CurrentDb
strSql = "SELECT Badge#, Date, Time FROM tblWhatever ORDER BY Badge#, Date,
Time"
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)

lngBadge = rst!badge#
Do Until rst.EOF
'get data from first of two records in set
dtmIn = MakeTime(rst!Date, rst!Time)

'get data from second of two records in set
rst.MoveNext
dtmOut = MakeTime(rst!Date, rst!Time)
sngHoursWorked = sngHoursWorked + (dtmOut - dtmIn)

'Check if the next set belongs to the same person
rst.MoveNext
If rst!badge# <> lngBadge# Then 'it's a different person,
'so assign the hours to the old person
'and start over for the new person.
Call AddHours(lngBadge#, sngHoursWorked)
rst.MoveNext
lngBadge = rst!badge#
sngHoursWorked = 0
End If
Loop

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Function

Function AddHours(lngBadge As Long, sngHoursWorked As Single)
'Your code to add to the payroll, etc goes here.
End Function

Function MakeTime(strDate, strTime) As Date
'MakeTime = The results of to convert to date/time from two fields.
'This will depend on the format of the data you get in.
End Function
--
Jeremy Wallace
AlphaBet City Dataworks
http://www.ABCDataworks.com
"Riegn Man" <r1********@yahoo.com> wrote in message
news:40*********************@news.frii.net...
I have a problem with access and our time clocks. We have time clocks
that put out a .log file with the badge swipes for everybody. There is
one .log file for each day. I am pulling that data into an access
database to manipulate it. In the table I have:

FIRSTNAME | LASTNAME | BADGE# | DATE | TIME

The problem is that the time clock doesn't differentiate between a
"clock in" and a "clock out". It only records badge swipes. So for
each person, I could have possibly 4-6 badge swipes per day. (clock-in
in the morning, out for lunch, in from lunch, out for the day, and if
someone needs to go to the doctor or something then they would clock out
and back in for that). Each badge swipe creates a new record in the
database. The only thing that changes is the date and time. So that
means that I could have up to 4-6 records per person, per day.
What I would like to do is separate those badge swipes, (database
records), into groups of 2 based on the badge number and then have
access do the math to tell how many hours are in between each clock in
and out. Then I would like to add the totals for all of the groups for
each person together and come up with a total time on the clock for the
day. As you can probably tell, this is for time-keeping/payroll.
I know how to get access to do the math, but I can't figure out how to
get it to separate the badge swipes into groups of 2. Is there anyone
out there with any ideas?
Thanks in advance.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #2

P: n/a
"Jeremy Wallace" <ab**********@AlphaBetCityDataworks.com> wrote in message news:<44********************@speakeasy.net>...
First off, what happens when someone forgets to clock out? Doesn't that
cause a ton of problems?

Next, what happens if someone works past midnight? Then there are records in
two different files.

Assuming the above are (somehow) not issues, you could just make a recordset
of the data ordered by person and time. Then you could walk that dataset in
code grabbing the data from two records at a time.

Here are three totally untested functions (well, one plus two shells). Do
note that the cases I mentioned above, and probably some others as well, may
well be significant issues, even if they only come up very rarely. I would
think a lot about what odditites there could be in the data and test the
heck out of your code so that you make sure it takes those situations into
accout. Mine doesn't deal with them at all. And, again, I've not even tried
running this code, so do be careful.

Jeremy

Function CalcTime()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim dtmIn As Date
Dim dtmOut As Date
Dim strSql As String
Dim lngBadge As Long
Dim sngHoursWorked As Single

Set db = CurrentDb
strSql = "SELECT Badge#, Date, Time FROM tblWhatever ORDER BY Badge#, Date,
Time"
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)

lngBadge = rst!badge#
Do Until rst.EOF
'get data from first of two records in set
dtmIn = MakeTime(rst!Date, rst!Time)

'get data from second of two records in set
rst.MoveNext
dtmOut = MakeTime(rst!Date, rst!Time)
sngHoursWorked = sngHoursWorked + (dtmOut - dtmIn)

'Check if the next set belongs to the same person
rst.MoveNext
If rst!badge# <> lngBadge# Then 'it's a different person,
'so assign the hours to the old person
'and start over for the new person.
Call AddHours(lngBadge#, sngHoursWorked)
rst.MoveNext
lngBadge = rst!badge#
sngHoursWorked = 0
End If
Loop

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Function

Function AddHours(lngBadge As Long, sngHoursWorked As Single)
'Your code to add to the payroll, etc goes here.
End Function

Function MakeTime(strDate, strTime) As Date
'MakeTime = The results of to convert to date/time from two fields.
'This will depend on the format of the data you get in.
End Function
--
Jeremy Wallace
AlphaBet City Dataworks
http://www.ABCDataworks.com
"Riegn Man" <r1********@yahoo.com> wrote in message
news:40*********************@news.frii.net...
I have a problem with access and our time clocks. We have time clocks
that put out a .log file with the badge swipes for everybody. There is
one .log file for each day. I am pulling that data into an access
database to manipulate it. In the table I have:

FIRSTNAME | LASTNAME | BADGE# | DATE | TIME

The problem is that the time clock doesn't differentiate between a
"clock in" and a "clock out". It only records badge swipes. So for
each person, I could have possibly 4-6 badge swipes per day. (clock-in
in the morning, out for lunch, in from lunch, out for the day, and if
someone needs to go to the doctor or something then they would clock out
and back in for that). Each badge swipe creates a new record in the
database. The only thing that changes is the date and time. So that
means that I could have up to 4-6 records per person, per day.
What I would like to do is separate those badge swipes, (database
records), into groups of 2 based on the badge number and then have
access do the math to tell how many hours are in between each clock in
and out. Then I would like to add the totals for all of the groups for
each person together and come up with a total time on the clock for the
day. As you can probably tell, this is for time-keeping/payroll.
I know how to get access to do the math, but I can't figure out how to
get it to separate the badge swipes into groups of 2. Is there anyone
out there with any ideas?
Thanks in advance.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


I have thought about these and other problems and have come up with
what I think is a working solution. I have got a query counting the
duplicate records in the badge number category. I then have it
checking to see if there are an even or odd number of duplicates. My
theory is that if there is an even number of dupes, then that person
must be clocked out. If there is an odd number of dupes, then that
person is still clocked in. It's kinda primitive, but it works. I
just have the query checking to see if the number of dupes divided by
2 is an integer. If it's a whole number then the number of dupes is
even. If it's a decimal number then the number of dupes is odd.
Anyway, if the number of badge punches is odd, it removes the records
and tells the user that there was an error in swiping the badge.
There will never be anyone that is clocked in after midnight, (well
there never has been anyway). I will have to deal with that if it
ever happens. I appreciate the help. I'm gonna try this and see what
I can get done.

Thanks again for all your help.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.