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!
