Calculating the time spent at work

  • Thread starter Thread starter Murray
  • Start date Start date
M

Murray

I'm trying to figure out how to calculate how long someone has been at
work. I have the record of the time they signed in, how do I retrieve
that and subtract it from the current time and save the elapsed hours
with the sign out record?
 
Check out the DateDiff function. It allows you to subtract time values. Best
to have it calculate the number of minutes and then you can convert that to
hours.

It's in the Help file; post back with questions.
 
Thanks for your help, I'll take a look at that. How do I get it to
open the old record that was saved say at 9am. If I have 6 people sign
in in the AM, then they sign out in the PM I need to know how I can
get John Smith's signin record to pull hi time to do the calculation.
I'm fairly new to Access :)
 
Not sure what you mean by "open the old record". I would envision that your
setup would use a query to calculate the time intervals, using a calculated
field in the query. The query would "open the record" as part of its job.
But perhaps I'm not comprehending.....?
 
You need other fields in the table to help identify a particular
sign-in based on date and employee, such as employee id and a date
stamp. Then you can create a query with criteria specifying the
employee(s) and/or date(s) you want to evaluate.
 
As part of the signin I have a lookup table set so the employees can
select their name. THat way, I know I won't have to worry that the
name is spelt wrong (typos). That name is stored along with the time
that they sign in at. If I have John Smith in a name column along with
say 9:00 am and a date in a time/date column I'd like to have another
form called signout that would use the employees name pull the time
that he signed in say 9:00am and calculate the length of time he was
there and have that go into the table say an elapsed time column. Is
that possible with Access? If so how do I do that? Is there a book I
can read that will help? The one that I have doesn't go into that much
detail. I'm guessing that I need a formula to go into one of the event
fields, but I could be wrong :) like I said, I'm new to Access, thanks
for your time.
 
It's certainly possible to use a query to find a record based on specific
criteria, such as a person's name (better, the person's ID) and the signin
or signout time.

You can design your query to use expressions for the "criteria:" for a field
in the query. This query then can be used to display information on a form
(and can even edit the info) or to display a report.

If this is your first foray into ACCESS, I agree that it would be good for
you to obtain a fundamental/introductory book on ACCESS (there are many
available in bookstores) in order to get an initial exposure to how to do
this. Then you can post back with questions about what you're trying, what's
not working, how to do something differently.
 
OK here is some test data that I've created using a form. I can't
figure out how to design a query that will tell me how long these
people were "at work" I have the table sorted on Date.


Name Date

Smith, John 11/18/03 11:51:26 AM
Jones, Mary 11/18/03 11:51:36 AM
Jones, Mary 11/18/03 11:51:47 AM
Smith, John 11/18/03 11:51:50 AM
Jones, Mary 11/18/03 6:46:57 PM
Smith, John 11/18/03 6:47:03 PM
Smith, John 11/18/03 6:47:10 PM
Jones, Mary 11/18/03 6:47:16 PM

Let me recap what it is I'm trying to do:

At the front there is a computer and I want people as they come in to
signin. I have a form that they can select there name from a combo
box/lookup table, the date ( =now() ) is hidden so they can't change
that.

When they go home, I'd like them to signout (using whatever method
you'd deem suitable) I'm using the same form and get the results
above. Again the time/date field is hidden. If I could figure out how
to add the signout time as they signout to their signin record then it
would be easy :)

What I'd then like to do is generate a report that has the name,
timein, timeout and elapsed time. (timein/out would include the date)

I have one book from the library that is for Office 2000 so it covers
Excel, Word, Power Point, Access . . . so it's not too in depth. It
does show how to do a calcualtion using a query and add that into
another column. I need to know how to manipulate the signin and
signout record. Access 2000 for Dummies is out from the library until
Nov 27th.

Could you recommend a book that would help? Give me a title and
author?


Ken Snell said:
It's certainly possible to use a query to find a record based on specific
criteria, such as a person's name (better, the person's ID) and the signin
or signout time.

You can design your query to use expressions for the "criteria:" for a field
in the query. This query then can be used to display information on a form
(and can even edit the info) or to display a report.

If this is your first foray into ACCESS, I agree that it would be good for
you to obtain a fundamental/introductory book on ACCESS (there are many
available in bookstores) in order to get an initial exposure to how to do
this. Then you can post back with questions about what you're trying, what's
not working, how to do something differently.
Click to expand...
=====SNIP
 
You're saying that the table's records do not indicate whether a record is a
signin or a signout record? What do you do for multiple days? Or are you
assuming that two records per day, with the same date, are always the in /
out pair?

The following SQL statement will show you the calculation that you wish, by
employee and by date:

SELECT [TableName].[Name], DateValue([TableName].[Date]) AS WorkDate,
(Max(TimeValue([TableName].[Date])) - Min(TimeValue([TableName].[Date]))) *
24 AS HoursWorked
FROM [TableName]
GROUP BY [TableName].[Name], DateValue([TableName].[Date]);

Note that it's not good practice to use Name and Date (and other reserved
words) as they're used by ACCESS for function and property and method names;
you can confuse ACCESS and cause problems. Use EmpName and WorkDate etc.

You can save the query above and then use that query as the record source of
a report to show the desired information.

As for books, there are lots of them out there.....Running Microsoft ACCESS
(version number), ACCESS (version number) Developer's Handbook, ACCESS
(version number) Bible, etc. Go to a bookstore and look through the
selection to find one that "connects" with you.

I don't know if the "dummy" book will be the best book to start with, but
..... ya never know.
 
No there won't be people signing in/out over multiple days, the place
opens at 8am and closes around 5pm. I'll give that SQL statement a try
and see and I'll change the names from Date and Name to the ones
you've suggested. I should have known better from my BASIC programming
days :)

Thanks for your help so far.

Ken Snell said:
You're saying that the table's records do not indicate whether a record is a
signin or a signout record? What do you do for multiple days? Or are you
assuming that two records per day, with the same date, are always the in /
out pair?

The following SQL statement will show you the calculation that you wish, by
employee and by date:

SELECT [TableName].[Name], DateValue([TableName].[Date]) AS WorkDate,
(Max(TimeValue([TableName].[Date])) - Min(TimeValue([TableName].[Date]))) *
24 AS HoursWorked
FROM [TableName]
GROUP BY [TableName].[Name], DateValue([TableName].[Date]);

Note that it's not good practice to use Name and Date (and other reserved
words) as they're used by ACCESS for function and property and method names;
you can confuse ACCESS and cause problems. Use EmpName and WorkDate etc.

You can save the query above and then use that query as the record source of
a report to show the desired information.

As for books, there are lots of them out there.....Running Microsoft ACCESS
(version number), ACCESS (version number) Developer's Handbook, ACCESS
(version number) Bible, etc. Go to a bookstore and look through the
selection to find one that "connects" with you.

I don't know if the "dummy" book will be the best book to start with, but
.... ya never know.

--
Ken Snell
<MS ACCESS MVP>


Murray said:
OK here is some test data that I've created using a form. I can't
figure out how to design a query that will tell me how long these
people were "at work" I have the table sorted on Date.


Name Date

Smith, John 11/18/03 11:51:26 AM
Jones, Mary 11/18/03 11:51:36 AM
Jones, Mary 11/18/03 11:51:47 AM
Smith, John 11/18/03 11:51:50 AM
Jones, Mary 11/18/03 6:46:57 PM
Smith, John 11/18/03 6:47:03 PM
Smith, John 11/18/03 6:47:10 PM
Jones, Mary 11/18/03 6:47:16 PM
Click to expand...
Click to expand...
=====SNIP
 
Back
Top