Time Calculations, MVP Opinions

[Dennis Ruppert]

Greetings

My Access/VBA learning process has been disjointed, spurred on by
necessity to my company, and hampered by time constraints to learn
properly. Consequently, I have learned some very complex stuff, yet I
am not up to par on some of the basics. Frustrating, and it makes it
difficult to ask for help. The most common answers I have recieved in
other newsgroups is most often: "Get this or that book and read....."
etc. Although this is obvious, it is not practical when under pressure
for results. I suspect I am not alone!

So, for the purpose of this post, please consider me a "newbie". But I
am not afraid of VBA. Whenever I get some sample code from someone, I
DO NOT use it until I understand it. I hope someone here can humor me,
and help me. I do keep learning, by asking questions. And I do plug
away at the books.....

I have an application that, in a nutshell, captures start and stop
times for workers in a production facility. The form uses the Now()
function, and the end result is a table with JobNo, StartTime,
EndTime, and ElapsedTime fields. I use the DateDiff function to
calculate the ElapsedTime.

I need a variety of reports. Other tables store information such as
PieceCount, OperatorName, WorkCentre, etc. I am able to get many of
these reports, using the Mod operator in my report source. But I am
stuck on the reports requiring calculations. For instance: ElapsedTime
divided by PieceCount equals AverageTimeEa.

Can anyone suggest a direction for me to go in? Should I be converting
all of this data to Decimal Numbers, doing the calculations, then
converting back to a Time format? Should I be doing this at a "query
level", or is there some functions I can use at the report level?
Ideally, I would like to end up with data I can export for others to
analyze, rather than results that are just formatted to read in a
printed report.

Can anyone suggest a "self help" source for me to research this
particular subject (Time). I have not been able to glean what I need
from Access Help.

And finally....I would be curious to hear the opinions of some of you
MVPs, regarding how Access handles Time. Surely this must be a very
common use of databases. Yet I have found it the most difficult area
of development. Is this just me?

Thanks in advance.... and sorry for the long-winded post!

Dennis

 

[John Vinson]

I have an application that, in a nutshell, captures start and stop
times for workers in a production facility. The form uses the Now()
function, and the end result is a table with JobNo, StartTime,
EndTime, and ElapsedTime fields. I use the DateDiff function to
calculate the ElapsedTime.

As a rule, storing the elapsed time is undesirable unless you've got a
really good reason to do so. Maybe yours is one of these exceptions,
since you're using the result extensively! What units are you using?
Minutes? Seconds?

I need a variety of reports. Other tables store information such as
PieceCount, OperatorName, WorkCentre, etc. I am able to get many of
these reports, using the Mod operator in my report source. But I am
stuck on the reports requiring calculations. For instance: ElapsedTime
divided by PieceCount equals AverageTimeEa.

For that you really need a numeric time. DateDiff returns long
integers; a division operation will return a Double Float (e.g.
3.21923 minutes/widget).

Can anyone suggest a direction for me to go in? Should I be converting
all of this data to Decimal Numbers, doing the calculations, then
converting back to a Time format?

I would recommend NOT using the Decimal datatype until Access support
for it gets a bit better! I've heard a number of complaints about it.
Use Long Integers, or Double for numbers with fractions.

Should I be doing this at a "query
level", or is there some functions I can use at the report level?

You can do the calculations at either level. The advantage of the
query level is that you can easily sort or search by the calculated
value; doing the calculation on the report may make the query run
faster since you're offloading the calculation.

Ideally, I would like to end up with data I can export for others to
analyze, rather than results that are just formatted to read in a
printed report.

The trouble is that a Date/Time datatype (stored internally as a
Double Float count of days and fractions of a day since midnight,
December 30, 1899... a date whereby hangs a tale) isn't really
designed for storing durations. It's good for storing an exact point
in time, but problematic otherwise!

Can anyone suggest a "self help" source for me to research this
particular subject (Time). I have not been able to glean what I need
from Access Help.

NOT surprising; the help indexing is WRETCHED and this is a rather
arcane branch of lore. I've pieced together quite a few tricks for
handling time on my own, from the newsgroups and elsewhere; but I
don't know of any specific reference.

And finally....I would be curious to hear the opinions of some of you
MVPs, regarding how Access handles Time. Surely this must be a very
common use of databases. Yet I have found it the most difficult area
of development. Is this just me?

Nope. It's a pain.

I've generally just done time calculations in (say) double float
minutes or seconds, and then written "ad hoc" expressions to display
in the familiar hours and minutes format. There are several ways to do
this, using the MOD functions for instance. I really should put
together some functions and make them available, or maybe someone
already has and I haven't stumbled upon them.

 

[PC Datasheet]

Dennis,

Start by going to your tables and checking all the "number' fields you want to
use in the calculations. Access defaults to a text datatype; you need to det the
data type to number when you want to do calculations.

If you need a kick start to get going or special help on a specific problem, I
am n business to provide customers help with Access. Email me at the address
below.

 

[Dennis Ruppert]

As a rule, storing the elapsed time is undesirable unless you've got a
really good reason to do so. Maybe yours is one of these exceptions,
since you're using the result extensively! What units are you using?
Minutes? Seconds?


For that you really need a numeric time. DateDiff returns long
integers; a division operation will return a Double Float (e.g.
3.21923 minutes/widget).


I would recommend NOT using the Decimal datatype until Access support
for it gets a bit better! I've heard a number of complaints about it.
Use Long Integers, or Double for numbers with fractions.


You can do the calculations at either level. The advantage of the
query level is that you can easily sort or search by the calculated
value; doing the calculation on the report may make the query run
faster since you're offloading the calculation.


The trouble is that a Date/Time datatype (stored internally as a
Double Float count of days and fractions of a day since midnight,
December 30, 1899... a date whereby hangs a tale) isn't really
designed for storing durations. It's good for storing an exact point
in time, but problematic otherwise!


NOT surprising; the help indexing is WRETCHED and this is a rather
arcane branch of lore. I've pieced together quite a few tricks for
handling time on my own, from the newsgroups and elsewhere; but I
don't know of any specific reference.


Nope. It's a pain.

I've generally just done time calculations in (say) double float
minutes or seconds, and then written "ad hoc" expressions to display
in the familiar hours and minutes format. There are several ways to do
this, using the MOD functions for instance. I really should put
together some functions and make them available, or maybe someone
already has and I haven't stumbled upon them.

Thanks John, for answering my long winded message.

You confirmed some things I suspected/feared. I will absorb your
thoughts, and watch for further posts. I did manage to find some
usable stuff in the microsoft.public.access.queries Group.

It seems as you are suggesting that I do not store the elapsed time in
my table, rather I should convert it at runtime to a Long Integer.
Then, I should do as many calculations, sort actions, grouping
actions, etc. as possible while in that format. Then finally, use Mod
operators, Format functions, etc. to report on the results. Is this a
fair capsulization of your reply?

There is stuff all over the web about this topic. Seems to me, a
complete book could be compiled on this subject alone! If anyone ever
wrote one that could walk a "newbie" through a "Time Oriented"
application, step by step, I know I would get in line to buy a copy.

Thanks again.

Dennis