Time Calculation For Particular Person On Particular Date

[A_Classic_Man]

I'm using MS Access 97. I am building a database (which will be split)
which will track the activities of 20 people. Three of the fields on
the form will be IDNumebr (text), Date (short date) and Time (short
time). What I am trying to do is have an unbound field on the form
that will show a total amount of time for the ID Number entered in the
IDNumber field on the date entered in the Date field. The IDNumber
field may have several entries on any given date. I would like the
unbound field to display the total number hours and minutes that have
already been entered for for the ID Number on the date entered in the
Date field.

How is this accomplished?
Thanks
Ron

 

[Graham Mandeno]

Hi Ron

For a start, there is no such thing as a "short date" or "short time" field.
A Date/Time field stores both a date *and* a time. It's possible for either
the date part or the time part to be zero if you really want to store just
one and not the other, but if you want to store both then it doesn't make
sense to use two fields.

It sounds like what you really need is a start time and a finish time. You
can then easily calculate the duration between the two using the DateDiff
function:

DateDiff( "s", [StartTime], [FinishTime] ) will give the duration in
seconds.

To convert to minutes or hours, divide by 60 or 3600.

So, you can write a query on your table to give the duration for each
record:

Select IDNumber, StartTime, FinishTime,
DateDiff( "s", [StartTime], [FinishTime] ) as Hours
from YourTable;

You can expand this query with a GROUP BY clause to add up all the times for
a particular day:

Select IDNumber, DateValue( [StartTime] ) as WorkDate,
Sum( DateDiff( "s", [StartTime], [FinishTime] ) ) as Hours
from YourTable group by IDNumber, DateValue( [StartTime] );

Note that the DateValue function returns only the date part of the field,
ignoring the time.

 

[A_Classic_Man]

Hi Ron

For a start, there is no such thing as a "short date" or "short time" field.
A Date/Time field stores both a date *and* a time.  It's possible for either
the date part or the time part to be zero if you really want to store just
one and not the other, but if you want to store both then it doesn't make
sense to use two fields.

It sounds like what you really need is a start time and a finish time.  You
can then easily calculate the duration between the two using the DateDiff
function:

DateDiff( "s", [StartTime], [FinishTime] ) will give the duration in
seconds.

To convert to minutes or hours, divide by 60 or 3600.

So, you can write a query on your table to give the duration for each
record:

Select IDNumber, StartTime, FinishTime,
    DateDiff( "s", [StartTime], [FinishTime] ) as Hours
    from YourTable;

You can expand this query with a GROUP BY clause to add up all the times for
a particular day:

Select IDNumber, DateValue( [StartTime] ) as WorkDate,
    Sum( DateDiff( "s", [StartTime], [FinishTime] ) ) as Hours
    from YourTable group by IDNumber, DateValue( [StartTime] );

Note that the DateValue function returns only the date part of the field,
ignoring the time.
--
Good Luck  

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm using MS Access 97. I am building a database (which will be split)
which will track the activities of 20 people. Three of the fields on
the form will be IDNumebr (text), Date (short date) and Time (short
time). What I am trying to do is have an unbound field on the form
that will show a total amount of time for the ID Number entered in the
IDNumber field on the date entered in the Date field. The IDNumber
field may have several entries on any given date. I would like the
unbound field to display the total number hours and minutes that have
already been entered for for the ID Number on the date entered in the
Date field.

How is this accomplished?
Thanks
Ron- Hide quoted text -

- Show quoted te

Thanks for trying but that's not what I'm looking for.

 

[Graham Mandeno]

Thanks for trying but that's not what I'm looking for.

So what ARE you looking for?

I understand you have three fields: an ID number, a date and a duration. To
get the total duration for a particular ID and date, you just need to sum
them.

But *how* is your duration being stored? In a date/time field? If so, then
this is a misuse of this type of field. A date/time is for storing a
*point* in time, not a *duration* between two points in time.

If you type "13:30:00" into a date/time field, what you are are actually
entering in "1.30pm on 30 December 1899". Using certain formatting, you can
make it *look* like 13 hours and 30 minutes, but it is not the same.

So, either you must store a start time and a finish time and calculate the
duration, or you must store the duration, but don't use a date/time field
for it.

I suggest you store the duration as a number of minutes (or seconds, if you
want a finer granularity). You can then write a function to format that
number of minutes (or seconds) however you please. For example:

Public Function FormatMinutes( ByVal lMins as Long ) as String
Dim s as String
If lMins >= 60 Then
s = Int(lMins / 60) & " hrs "
End If
s = s & (lMins Mod 60) & " mins"
FormatMinutes = s
End If

Because this field is now a simple number, you can sum it across a domain
and display the formatted result.

For example, 175 + 247 = 422

and

FormatMinutes(175) = "2 hrs 55 mins"
FormatMinutes(247) = "4 hrs 17 mins"
FormatMinutes(422) = "7 hrs 12 mins"

You can use DSum to sum the fields from records which match a given ID
number and date.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Ron

For a start, there is no such thing as a "short date" or "short time"
field.
A Date/Time field stores both a date *and* a time. It's possible for
either
the date part or the time part to be zero if you really want to store just
one and not the other, but if you want to store both then it doesn't make
sense to use two fields.

It sounds like what you really need is a start time and a finish time. You
can then easily calculate the duration between the two using the DateDiff
function:

DateDiff( "s", [StartTime], [FinishTime] ) will give the duration in
seconds.

To convert to minutes or hours, divide by 60 or 3600.

So, you can write a query on your table to give the duration for each
record:

Select IDNumber, StartTime, FinishTime,
DateDiff( "s", [StartTime], [FinishTime] ) as Hours
from YourTable;

You can expand this query with a GROUP BY clause to add up all the times
for
a particular day:

Select IDNumber, DateValue( [StartTime] ) as WorkDate,
Sum( DateDiff( "s", [StartTime], [FinishTime] ) ) as Hours
from YourTable group by IDNumber, DateValue( [StartTime] );

Note that the DateValue function returns only the date part of the field,
ignoring the time.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm using MS Access 97. I am building a database (which will be split)
which will track the activities of 20 people. Three of the fields on
the form will be IDNumebr (text), Date (short date) and Time (short
time). What I am trying to do is have an unbound field on the form
that will show a total amount of time for the ID Number entered in the
IDNumber field on the date entered in the Date field. The IDNumber
field may have several entries on any given date. I would like the
unbound field to display the total number hours and minutes that have
already been entered for for the ID Number on the date entered in the
Date field.

How is this accomplished?
Thanks
Ron- Hide quoted text -

- Show quoted te

Thanks for trying but that's not what I'm looking for.

 

[A_Classic_Man]

Thanks for trying but that's not what I'm looking for.

So what ARE you looking for?

I understand you have three fields: an ID number, a date and a duration.  To
get the total duration for a particular ID and date, you just need to sum
them.

But *how* is your duration being stored?  In a date/time field?  If so, then
this is a misuse of this type of field.  A date/time is for storing a
*point* in time, not a *duration* between two points in time.

If you type "13:30:00" into a date/time field, what you are are actually
entering in "1.30pm on 30 December 1899".  Using certain formatting, youcan
make it *look* like 13 hours and 30 minutes, but it is not the same.

So, either you must store a start time and a finish time and calculate the
duration, or you must store the duration, but don't use a date/time field
for it.

I suggest you store the duration as a number of minutes (or seconds, if you
want a finer granularity).  You can then write a function to format that
number of minutes (or seconds) however you please.  For example:

Public Function FormatMinutes( ByVal lMins as Long ) as String
Dim s as String
If lMins >= 60 Then
    s = Int(lMins / 60) & " hrs "
End If
s = s & (lMins Mod 60) & " mins"
FormatMinutes = s
End If

Because this field is now a simple number, you can sum it across a domain
and display the formatted result.

For example, 175 + 247 = 422

and

FormatMinutes(175) = "2 hrs 55 mins"
FormatMinutes(247) = "4 hrs 17 mins"
FormatMinutes(422) = "7 hrs 12 mins"

You can use DSum to sum the fields from records which match a given ID
number and date.
--
Good Luck  

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Ron

For a start, there is no such thing as a "short date" or "short time"
field.
A Date/Time field stores both a date *and* a time. It's possible for
either
the date part or the time part to be zero if you really want to store just
one and not the other, but if you want to store both then it doesn't make
sense to use two fields.

It sounds like what you really need is a start time and a finish time. You
can then easily calculate the duration between the two using the DateDiff
function:

DateDiff( "s", [StartTime], [FinishTime] ) will give the duration in
seconds.

To convert to minutes or hours, divide by 60 or 3600.

So, you can write a query on your table to give the duration for each
record:

Select IDNumber, StartTime, FinishTime,
DateDiff( "s", [StartTime], [FinishTime] ) as Hours
from YourTable;

You can expand this query with a GROUP BY clause to add up all the times
for
a particular day:

Select IDNumber, DateValue( [StartTime] ) as WorkDate,
Sum( DateDiff( "s", [StartTime], [FinishTime] ) ) as Hours
from YourTable group by IDNumber, DateValue( [StartTime] );

Note that the DateValue function returns only the date part of the field,
ignoring the time.

Graham Mandeno [Access MVP]
Auckland, New Zealand

- Show quoted te

Thanks for trying but that's not what I'm looking for.- Hide quoted text -

- Show quoted text -

Hello Graham

Just so we're on the same page here, the time that will be entered
into the "Time" field is a total amount of time it took them to
complete the assignment. In other words the job took them one hour and
thirty minutes. What I am hearing you say is that I cannot have them
enter 01:00 in the field. Here's what I wanting if my thick skull will
allow it.
ID Number Date
Time Unbound Field
First entry 445 05/12/2008
01:00 01:00
2nd entry 445 05/12/2008
03:00 04:00
3rd entry 445 05/12/2008
01:30 05:30
4th entry(new day) 445 05/13/2008
02:00 02:00
5th entry 445 05/13/2008
01:00 03:00

How I need to need to record the time is 1.0, 3.0, 1.5, etc. and store
the times in a number field, Right? Is it possible to write an
expression in the unbound field to update it? I am using a command
button on the form to go to "a new record" for the next entry?

Thanks for the patience
Ron

 

[Graham Mandeno]

Hi Ron

So you are currently storing the time in a date/time field, right? And your
two fields are named "Date" and "Time"?

My first advice (which is not really related to the immediate question) is
to rename these two fields. They could be "DateWorked" and "TimeWorked" or
something like that. The reason is that "Date" and "Time" are reserved
words (they are names of VB functions) and if you continue to use them then
in the future it will cause confusion or even failure of your application.

Next, as I said, storing a time duration in a date/time field is a misuse of
that data type. However, it *can* work, provided you understand what you
are doing. This course of action may have certain advantages, such at
automatic formatting and ease of data entry.

The data/time field is actually just a double-precision floating point
number, where the whole number part is the number of days since (or before
if it's negative) 30 Dec 1899. (Don't ask me why that day was chosen!) The
decimal part of the number is the fraction of a day that has elapsed since
midnight on the given date.

So:
0.25 = 6am on 30 Dec 1899 6am (one quarter of a day after midnight)
1.5 = noon on 31 Dec 1899
39581.75 = 6pm today (13 May 2008)

Now, IF you choose a date format that doesn't include and day, month or year
components and IF none of your time intervals is as great as 24 hours, then
you CAN use a date/time field to store the time interval.

This means that you can enter 1:30:00 and have it interpreted as 1.5 hours.

Furthermore, 1:30:00 + 2:25:00 = 3:55:00.

However, the problem comes when you have:
15:00:00 + 10:00:00
This does not give the expected 25:00:00, or even 1-01:00:00, but instead
you get:
31 Dec 1899 01:00:00
or, if you are using a format such as hh:nn:ss you just get 01:00:00.

So, with all those caveats in mind, you could set the controlsource of your
unbound textbox to:
=DSum( "TimeWorked", "YourTable", "IDNumber='" & [IDNumber] &
"' and DateWorked=" & Format([DateWorked], "\#yyyy-mm-dd\#")

Note the single quotes on either side of [IDNumber]. These should be
removed if IDNumber is a numeric field, not text as you stated in your first
post.

It's unfortunate that Access does not have a data type that is specifically
designed for storing time intervals.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


On May 12, 6:14 pm, "Graham Mandeno" <[email protected]>
wrote:

[snip!]

- Show quoted text -

Hello Graham

Just so we're on the same page here, the time that will be entered
into the "Time" field is a total amount of time it took them to
complete the assignment. In other words the job took them one hour and
thirty minutes. What I am hearing you say is that I cannot have them
enter 01:00 in the field. Here's what I wanting if my thick skull will
allow it.
ID Number Date
Time Unbound Field
First entry 445 05/12/2008
01:00 01:00
2nd entry 445 05/12/2008
03:00 04:00
3rd entry 445 05/12/2008
01:30 05:30
4th entry(new day) 445 05/13/2008
02:00 02:00
5th entry 445 05/13/2008
01:00 03:00

How I need to need to record the time is 1.0, 3.0, 1.5, etc. and store
the times in a number field, Right? Is it possible to write an
expression in the unbound field to update it? I am using a command
button on the form to go to "a new record" for the next entry?

Thanks for the patience
Ron

 

[A_Classic_Man]

Hi Ron

So you are currently storing the time in a date/time field, right?  And your
two fields are named "Date" and "Time"?

My first advice (which is not really related to the immediate question) is
to rename these two fields.  They could be "DateWorked" and "TimeWorked"or
something like that.  The reason is that "Date" and "Time" are reserved
words (they are names of VB functions) and if you continue to use them then
in the future it will cause confusion or even failure of your application.

Next, as I said, storing a time duration in a date/time field is a misuse of
that data type.  However, it *can* work, provided you understand what you
are doing.  This course of action may have certain advantages, such at
automatic formatting and ease of data entry.

The data/time field is actually just a double-precision floating point
number, where the whole number part is the number of days since (or before
if it's negative) 30 Dec 1899.  (Don't ask me why that day was chosen!)  The
decimal part of the number is the fraction of a day that has elapsed since
midnight on the given date.

So:
    0.25 = 6am on 30 Dec 1899 6am (one quarter of a day after midnight)
    1.5 = noon on 31 Dec 1899
    39581.75 = 6pm today (13 May 2008)

Now, IF you choose a date format that doesn't include and day, month or year
components and IF none of your time intervals is as great as 24 hours, then
you CAN use a date/time field to store the time interval.

This means that you can enter 1:30:00 and have it interpreted as 1.5 hours..

Furthermore, 1:30:00 + 2:25:00 = 3:55:00.

However, the problem comes when you have:
    15:00:00 + 10:00:00
This does not give the expected 25:00:00, or even 1-01:00:00, but instead
you get:
    31 Dec 1899 01:00:00
or, if you are using a format such as hh:nn:ss you just get 01:00:00.

So, with all those caveats in mind, you could set the controlsource of your
unbound textbox to:
    =DSum( "TimeWorked", "YourTable", "IDNumber='" & [IDNumber] &
        "' and DateWorked=" & Format([DateWorked], "\#yyyy-mm-dd\#")

Note the single quotes on either side of [IDNumber].  These should be
removed if IDNumber is a numeric field, not text as you stated in your first
post.

It's unfortunate that Access does not have a data type that is specifically
designed for storing time intervals.
--
Good Luck  

Graham Mandeno [Access MVP]
Auckland, New Zealand


On May 12, 6:14 pm, "Graham Mandeno" <[email protected]>
wrote:

[snip!]

- Show quoted text -

Hello Graham

Just so we're on the same page here, the time that will be entered
into the "Time" field is a total amount of time it took them to
complete the assignment. In other words the job took them one hour and
thirty minutes. What I am hearing you say is that I cannot have them
enter 01:00 in the field. Here's what I wanting if my thick skull will
allow it.
                        ID Number          Date
Time              Unbound Field
First entry             445                05/12/2008
01:00                    01:00
2nd entry              445                05/12/2008
03:00                    04:00
3rd entry               445                05/12/2008
01:30                    05:30
4th entry(new day) 445                05/13/2008
02:00                    02:00
5th entry               445                05/13/2008
01:00                    03:00

How I need to need to record the time is 1.0, 3.0, 1.5, etc. and store
the times in a number field, Right?  Is it possible to write an
expression in the unbound field to update it?  I am using a command
button on the form to go to "a new record" for the next entry?

Thanks for the patience
Ron

Graham

It was tough but you finally got through to me. I'm going to change
the Time field to a "General Number" format and use 1.5, 1.25, etc.
and then see if I can get your DSum suggestion to work.

It may be a day or two before I get back to you, but I'll either be
back to let you know how it worked or looking for more advise.

Many Thanks To You

Ron