Comparing data in ms access

[Riegnman]

Hey guys, I'm in need of a little help. I am very new to access but
have been trying to learn. My problem is as follows. . .

We have time clocks that dump the badge punches into a .log file on
one of the servers. I then use access to import these .log files and
separate the data into various tables so that I can manipulate it.
The problem is that the time clock just records punches and not "in"
and "out" times. The data that comes into the tables will have 2 or
4, (depending on if they clock out for lunch or not), entries with the
same date but different times for each badge number. I have gotten
the database to pull out the data and separate it by individual badge
numbers. I can then print a report with the employee name and the
clock punches. But with each day, I have 2 or 4 punches under each
badge number
What I want to know is:

1. Is there a way to compare that data, (that is separated by badge
number), by date?
2. If so, can I take all the records with the same date, and subtract
the "clock out" time, (latest time), from the "clock in" time,
(earliest time)? I know that I can use the formula (clock out time –
clock in time)*24 to get the total hours, but I have to have the
database differentiate between clock in and clock out times.

My goal is to have the database print out a report from the time clock
that will print:

Name Badge Number Date Clock In Clock Out Total Hours

That way I can have a database that will print an automatic time card
that the hourly employees can then take to the supervisor to be
approved. This will take a lot of work off of our HR dept. as now
the employees are having to manually fill out a paper timecard and the
HR dept. has to input the time manually into a spreadsheet to figure
out the hours and then send that data to the people that handle
payroll.

Thanks for all you help

[rkc]

"Riegnman" <r1********@yahoo.com> wrote in message
news:6f*************************@posting.google.co m...

Hey guys, I'm in need of a little help. I am very new to access but
have been trying to learn. My problem is as follows. . .

We have time clocks that dump the badge punches into a .log file on
one of the servers. I then use access to import these .log files and
separate the data into various tables so that I can manipulate it.
The problem is that the time clock just records punches and not "in"
and "out" times. The data that comes into the tables will have 2 or
4, (depending on if they clock out for lunch or not), entries with the
same date but different times for each badge number. I have gotten
the database to pull out the data and separate it by individual badge
numbers. I can then print a report with the employee name and the
clock punches. But with each day, I have 2 or 4 punches under each
badge number
What I want to know is:

1. Is there a way to compare that data, (that is separated by badge
number), by date?
2. If so, can I take all the records with the same date, and subtract
the "clock out" time, (latest time), from the "clock in" time,
(earliest time)? I know that I can use the formula (clock out time -
clock in time)*24 to get the total hours, but I have to have the
database differentiate between clock in and clock out times.

What does the data in the .log file look like?
What does a row in the table(s) it ends up in look like?

[Wayne Reynolds]

I have figured out sort of how to make this work. I am just using a
query with the “min” “max” feature in the queries. But that still
leaves me with one problem. I have people that hit the clock in the
morning, clock out for lunch, clock back in from lunch and then clock
out for the day. With my solution, I can only pull 2 times from the
table and the other 2 go out the window and can’t be used. It pulls the
earliest time and the latest time, but not the clock out and in for
lunch. I had thought about just taking the min and max times, adding
them together and then subtracting an hour for lunch. But, what if they
take more than an hour for lunch or go to the doctor or something?
Anyway, to answer your question, here’s what the data looks like in the
log file and what it looks like when I pull it into a table.

!! \A! PN00 040104092741 0100AW0228011058
the 040104 is the date (dd:mm:yy), the 092741 is the time (hh:mm:ss)
and the 11058 at the end is the badge number. The rest is just
identifiers telling me which time clock they hit and the last time the
timeclock sent data to the server.

There will be one line just like this for each time someone swipes their
badge. All of the records for the whole day are kept in one .log file.
The .log file is named with the date, (ie. data0224.log). I have gotten
it to pull the .log files in automatically, three times a day and set
them in a table with 2 fields, (badge number, date/time). I then
separate the date/time into date and time. That gives me 2-6, (6 if
they have a doctors appt. or such), records per person, per day.

My ultimate goal is to be able to separate the data into series of 2
swipes. That way I can take the clock in and clock out, clock out for
lunch and clock in from lunch; add each of the two together to get a
number of hours worked and then add the two totals together to get a
total number worked for the day.
I’m thinking that I can figure out some way to tell access that if there
are an uneven number of swipes for the day, (or perhaps if the number of
swipes is prime), then to display a message that the person did not
clock in or out correctly. I can then deal with those on an individual
basis.
I know that I am probably in over my head, but I am enjoying the
learning curve and all the help that the great people in the programming
community offer.
Any help would be greatly appreciated.

Thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Wayne Reynolds]

I have figured out sort of how to make this work. I am just using a
query with the “min” “max” feature in the queries. But that still
leaves me with one problem. I have people that hit the clock in the
morning, clock out for lunch, clock back in from lunch and then clock
out for the day. With my solution, I can only pull 2 times from the
table and the other 2 go out the window and can’t be used. It pulls the
earliest time and the latest time, but not the clock out and in for
lunch. I had thought about just taking the min and max times, adding
them together and then subtracting an hour for lunch. But, what if they
take more than an hour for lunch or go to the doctor or something?
Anyway, to answer your question, here’s what the data looks like in the
log file and what it looks like when I pull it into a table.

!! \A! PN00 040104092741 0100AW0228011058
the 040104 is the date (dd:mm:yy), the 092741 is the time (hh:mm:ss)
and the 11058 at the end is the badge number. The rest is just
identifiers telling me which time clock they hit and the last time the
timeclock sent data to the server.

There will be one line just like this for each time someone swipes their
badge. All of the records for the whole day are kept in one .log file.
The .log file is named with the date, (ie. data0224.log). I have gotten
it to pull the .log files in automatically, three times a day and set
them in a table with 2 fields, (badge number, date/time). I then
separate the date/time into date and time. That gives me 2-6, (6 if
they have a doctors appt. or such), records per person, per day.

My ultimate goal is to be able to separate the data into series of 2
swipes. That way I can take the clock in and clock out, clock out for
lunch and clock in from lunch; add each of the two together to get a
number of hours worked and then add the two totals together to get a
total number worked for the day.
I’m thinking that I can figure out some way to tell access that if there
are an uneven number of swipes for the day, (or perhaps if the number of
swipes is prime), then to display a message that the person did not
clock in or out correctly. I can then deal with those on an individual
basis.
I know that I am probably in over my head, but I am enjoying the
learning curve and all the help that the great people in the programming
community offer.
Any help would be greatly appreciated.

Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mark Reed]

Hi Wayne,
This is just a stab in the dark but as I figure it, it would be unlikely
that an employee would swipe more than twice within an hour. With this in
mind could you not carry on using the min/max as you have but add
datepart("H",your_table].[your_field]) into your query. That way, it will
split the swipe times into hourly blocks with the min and max for that hour.

"Wayne Reynolds" <r1********@yahoo.com> wrote in message
news:40*********************@news.frii.net...
I have figured out sort of how to make this work. I am just using a
query with the "min" "max" feature in the queries. But that still
leaves me with one problem. I have people that hit the clock in the
morning, clock out for lunch, clock back in from lunch and then clock
out for the day. With my solution, I can only pull 2 times from the
table and the other 2 go out the window and can't be used. It pulls the
earliest time and the latest time, but not the clock out and in for
lunch. I had thought about just taking the min and max times, adding
them together and then subtracting an hour for lunch. But, what if they
take more than an hour for lunch or go to the doctor or something?
Anyway, to answer your question, here's what the data looks like in the
log file and what it looks like when I pull it into a table.

!! \A! PN00 040104092741 0100AW0228011058
the 040104 is the date (dd:mm:yy), the 092741 is the time (hh:mm:ss)
and the 11058 at the end is the badge number. The rest is just
identifiers telling me which time clock they hit and the last time the
timeclock sent data to the server.

There will be one line just like this for each time someone swipes their
badge. All of the records for the whole day are kept in one .log file.
The .log file is named with the date, (ie. data0224.log). I have gotten
it to pull the .log files in automatically, three times a day and set
them in a table with 2 fields, (badge number, date/time). I then
separate the date/time into date and time. That gives me 2-6, (6 if
they have a doctors appt. or such), records per person, per day.

My ultimate goal is to be able to separate the data into series of 2
swipes. That way I can take the clock in and clock out, clock out for
lunch and clock in from lunch; add each of the two together to get a
number of hours worked and then add the two totals together to get a
total number worked for the day.
I'm thinking that I can figure out some way to tell access that if there
are an uneven number of swipes for the day, (or perhaps if the number of
swipes is prime), then to display a message that the person did not
clock in or out correctly. I can then deal with those on an individual
basis.
I know that I am probably in over my head, but I am enjoying the
learning curve and all the help that the great people in the programming
community offer.
Any help would be greatly appreciated.

Thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Riegn Man]

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Riegnman]

Mark,
I don't think that will work because it will give me the same
result that i have now. I am now using the min/max on the time field.
It goes by the hour in the time to display the min and max for each
person for each day. So all it will do is give me the first clock
punch and the last punch. I need whatever is in the middle. Thanks
for the suggestion.

"Mark Reed" <ma*********@ntlworld.com> wrote in message news:<l1I_b.18551$ft.9620@newsfe1-win>...

Hi Wayne,
This is just a stab in the dark but as I figure it, it would be unlikely
that an employee would swipe more than twice within an hour. With this in
mind could you not carry on using the min/max as you have but add
datepart("H",your_table].[your_field]) into your query. That way, it will
split the swipe times into hourly blocks with the min and max for that hour.

"Wayne Reynolds" <r1********@yahoo.com> wrote in message
news:40*********************@news.frii.net...
I have figured out sort of how to make this work. I am just using a
query with the "min" "max" feature in the queries. But that still
leaves me with one problem. I have people that hit the clock in the
morning, clock out for lunch, clock back in from lunch and then clock
out for the day. With my solution, I can only pull 2 times from the
table and the other 2 go out the window and can't be used. It pulls the
earliest time and the latest time, but not the clock out and in for
lunch. I had thought about just taking the min and max times, adding
them together and then subtracting an hour for lunch. But, what if they
take more than an hour for lunch or go to the doctor or something?
Anyway, to answer your question, here's what the data looks like in the
log file and what it looks like when I pull it into a table.

!! \A! PN00 040104092741 0100AW0228011058
the 040104 is the date (dd:mm:yy), the 092741 is the time (hh:mm:ss)
and the 11058 at the end is the badge number. The rest is just
identifiers telling me which time clock they hit and the last time the
timeclock sent data to the server.

There will be one line just like this for each time someone swipes their
badge. All of the records for the whole day are kept in one .log file.
The .log file is named with the date, (ie. data0224.log). I have gotten
it to pull the .log files in automatically, three times a day and set
them in a table with 2 fields, (badge number, date/time). I then
separate the date/time into date and time. That gives me 2-6, (6 if
they have a doctors appt. or such), records per person, per day.

My ultimate goal is to be able to separate the data into series of 2
swipes. That way I can take the clock in and clock out, clock out for
lunch and clock in from lunch; add each of the two together to get a
number of hours worked and then add the two totals together to get a
total number worked for the day.
I'm thinking that I can figure out some way to tell access that if there
are an uneven number of swipes for the day, (or perhaps if the number of
swipes is prime), then to display a message that the person did not
clock in or out correctly. I can then deal with those on an individual
basis.
I know that I am probably in over my head, but I am enjoying the
learning curve and all the help that the great people in the programming
community offer.
Any help would be greatly appreciated.

Thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[R.A.Balder]

I think you need another aproach to what you want.
As I understand, you wan't to create statistics on the number of hours /
time people have been at work.
Think about events. An event is one person coming or going.
As one person arrives in the morning is his event number one. He/she leaves
for lunch is event number 2. The time stamp for event no 2 minus time stamp
for event no 1 = time spent at work.
By modelling this way, you should be able to sum up the no of hours at work
each day for each person. And you might check some rules - like there must
be an even numbers of events for each person (he / she can't arrive without
leaving - if so check his desk and see if he is still alive.)
You will get in trouble if you have people working past midnight (and a lot
of systems for time registration don't handle that.)

To do what I have described, you might add a field in the table named
'Event', and use and update query to number the events pr person pr day.
Then you might use a Cross Tab query to sum up the number of hours between
the evnt for each person.
Best luck
Rolfern

"Riegnman" <r1********@yahoo.com> wrote in message
news:6f**************************@posting.google.c om...

Mark,
I don't think that will work because it will give me the samee
result that i have now. I am now using the min/max on the time field.
It goes by the hour in the time to display the min and max for each
person for each day. So all it will do is give me the first clock
punch and the last punch. I need whatever is in the middle. Thanks
for the suggestion.

"Mark Reed" <ma*********@ntlworld.com> wrote in message

news:<l1I_b.18551$ft.9620@newsfe1-win>...

Hi Wayne,
This is just a stab in the dark but as I figure it, it would be unlikely that an employee would swipe more than twice within an hour. With this in mind could you not carry on using the min/max as you have but add
datepart("H",your_table].[your_field]) into your query. That way, it will split the swipe times into hourly blocks with the min and max for that hour.
"Wayne Reynolds" <r1********@yahoo.com> wrote in message
news:40*********************@news.frii.net...
I have figured out sort of how to make this work. I am just using a
query with the "min" "max" feature in the queries. But that still
leaves me with one problem. I have people that hit the clock in the
morning, clock out for lunch, clock back in from lunch and then clock
out for the day. With my solution, I can only pull 2 times from the
table and the other 2 go out the window and can't be used. It pulls the
earliest time and the latest time, but not the clock out and in for
lunch. I had thought about just taking the min and max times, adding
them together and then subtracting an hour for lunch. But, what if they
take more than an hour for lunch or go to the doctor or something?
Anyway, to answer your question, here's what the data looks like in the
log file and what it looks like when I pull it into a table.

!! \A! PN00 040104092741 0100AW0228011058
the 040104 is the date (dd:mm:yy), the 092741 is the time (hh:mm:ss)
and the 11058 at the end is the badge number. The rest is just
identifiers telling me which time clock they hit and the last time the
timeclock sent data to the server.

There will be one line just like this for each time someone swipes their
badge. All of the records for the whole day are kept in one .log file.
The .log file is named with the date, (ie. data0224.log). I have gotten
it to pull the .log files in automatically, three times a day and set
them in a table with 2 fields, (badge number, date/time). I then
separate the date/time into date and time. That gives me 2-6, (6 if
they have a doctors appt. or such), records per person, per day.

My ultimate goal is to be able to separate the data into series of 2
swipes. That way I can take the clock in and clock out, clock out for
lunch and clock in from lunch; add each of the two together to get a
number of hours worked and then add the two totals together to get a
total number worked for the day.
I'm thinking that I can figure out some way to tell access that if there
are an uneven number of swipes for the day, (or perhaps if the number of
swipes is prime), then to display a message that the person did not
clock in or out correctly. I can then deal with those on an individual
basis.
I know that I am probably in over my head, but I am enjoying the
learning curve and all the help that the great people in the programming
community offer.
Any help would be greatly appreciated.

Thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ben]

I once wrote a time card system similar to the one you described..

It did a whole load of time analysis on 100+ employees.

Logging swipes and analysing the results

Because access can get confused with storing dates/times etc I decided the best way was to store the time as a text

I then wrote functions like these below to process time calculations etc.

The beauty of this is that you can use isDate to check if the string is in Time format or not, this allows you to put text comments in with times, for example SICK/HOLIDAY etc.

if isDate returnds true then you are dealing with a time so use the following funcions...

Hope this helps inspire you

good luck

Ben

Function TextTimeMoreThan(ByVal t1, ByVal t2) As Boolean
On Error Resume Next
t1hrs = Val(Left(t1, InStr(1, t1, ":") - 1))
t1mins = Val(Left(Right(t1, Len(t1) - InStr(1, t1, ":")), 2))

t2hrs = Val(Left(t2, InStr(1, t2, ":") - 1))
t2mins = Val(Left(Right(t2, Len(t2) - InStr(1, t2, ":")), 2))

If t1hrs > t2hrs Then
TextTimeMoreThan = True
Exit Function
End If

If t1hrs = t2hrs Then
If t1mins > t2mins Then
TextTimeMoreThan = True
Exit Function
End If
End If
TextTimeMoreThan = False
End Function

Function TextTimeMoreThanBy(ByVal t1, ByVal t2)
On Error Resume Next

If TextTimeMoreThan(t1, t2) Then
TextTimeMoreThanBy = SubtractTextTimes(t1, t2)
Exit Function
Else
TextTimeMoreThanBy = "0:00"
End If
End Function
Function SubtractTextTimes(ByVal t1, ByVal t2)
On Error Resume Next
If Not TextTimeMoreThan(t1, t2) Then
tmpT = t1
t1 = t2
t2 = tmpT
End If

t1hrs = Val(Left(t1, InStr(1, t1, ":") - 1))
t1mins = Val(Left(Right(t1, Len(t1) - InStr(1, t1, ":")), 2))

t2hrs = Val(Left(t2, InStr(1, t2, ":") - 1))
t2mins = Val(Left(Right(t2, Len(t2) - InStr(1, t2, ":")), 2))

hrsdiff = Abs(t1hrs) - Abs(t2hrs)
minsdiff = t1mins - t2mins

If minsdiff < 0 Then
hrsdiff = hrsdiff - 1
minsdiff = minsdiff + 60
End If

SubtractTextTimes = Format(hrsdiff, "00") & ":" & Format(minsdiff, "00")
End Function

Function AddTextTimes(ByVal t1, ByVal t2)
On Error Resume Next
t1hrs = Left(t1, InStr(1, t1, ":") - 1)
t1mins = Left(Right(t1, Len(t1) - InStr(1, t1, ":")), 2)

t2hrs = Left(t2, InStr(1, t2, ":") - 1)
t2mins = Left(Right(t2, Len(t2) - InStr(1, t2, ":")), 2)

totMins = Val(t1mins) + Val(t2mins)
totMinsT = totMins Mod 60
totHrs = Val(t1hrs) + Val(t2hrs) + Fix(totMins / 60)
AddTextTimes = totHrs & ":" & Format(totMinsT, "00")
End Function