Time Elapsed & Average

[David S]

Here is my problem and scenairo: I have used access to link to a sql
database table in hopes to do some response and average time
queries/calculations. What I soon discovered was there was a only one date
field for storing the date as a date/time, and three separate field for
storing times (received,dispatched, arrived.) However the times are being
stored as text (at least this is how Access in interpeted the fields when I
made my link.) Of course, I have no control over how the values are being
stored in the sql database. What I would like to do first is simply see the
elapse time between two times in a time format of 1.6 or 1.7 or .5 minutes.
(Taking in considerations the midnight switch. I have only a single date).
I then would like to be able to perform some averaging to see what my various
response times are. Also, having it displayed/formated as aforemention.

I have used the datediff function and have been able to get some results in
the format I want except I don't know how to handle the negative
values(results from the midnight changeover.) I am using the below in a
query

Result: DateDiff("s",[rcvtime],[cleartime])/60

But because of data types and how I would like it displayed I cannot manage
the averaging.

I hope I have provided enough information and that it makes sense. Any help
would be appreciated.

Thanks,
David Shertzer
(e-mail address removed)

 

[Jeff Boyce]

It isn't clear to me yet how you can be using the DateDiff() function if the
times are text, not a date/time field.

If you subtract two numbers and get a negative number, you could use the
Abs() (absolute value) function to convert all answers to their positive
value equivalent.

If you are trying to handle a time span that crosses midnight, perhaps you
could add 12 hours (I'd need to know more about your data, but that's a
possibility) to the 'after midnight' time value.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[David S]

Jeff,

I don't know how the DateDiff() funciton is working, but it does. And that
is what one of those things that has me puzzled.

When I returned back to my office today, I examined my data closer. Hope
this additional info might help. What I discovered is that the Sql database
table that is storing the data that contain the "time" fields is a "varchar"
datatype. When I link to that table Access2003 is interpreting the fields as
text.

I have not had an opportunity to try what you suggested yet. I still don't
have the average thing yet but I believe I need to resolve the other before I
can move on to that.

Still stump & puzzled.

It isn't clear to me yet how you can be using the DateDiff() function if the
times are text, not a date/time field.

If you subtract two numbers and get a negative number, you could use the
Abs() (absolute value) function to convert all answers to their positive
value equivalent.

If you are trying to handle a time span that crosses midnight, perhaps you
could add 12 hours (I'd need to know more about your data, but that's a
possibility) to the 'after midnight' time value.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Here is my problem and scenairo: I have used access to link to a sql
database table in hopes to do some response and average time
queries/calculations. What I soon discovered was there was a only one date
field for storing the date as a date/time, and three separate field for
storing times (received,dispatched, arrived.) However the times are being
stored as text (at least this is how Access in interpeted the fields when I
made my link.) Of course, I have no control over how the values are being
stored in the sql database. What I would like to do first is simply see the
elapse time between two times in a time format of 1.6 or 1.7 or .5 minutes.
(Taking in considerations the midnight switch. I have only a single date).
I then would like to be able to perform some averaging to see what my various
response times are. Also, having it displayed/formated as aforemention.

I have used the datediff function and have been able to get some results in
the format I want except I don't know how to handle the negative
values(results from the midnight changeover.) I am using the below in a
query

Result: DateDiff("s",[rcvtime],[cleartime])/60

But because of data types and how I would like it displayed I cannot manage
the averaging.

I hope I have provided enough information and that it makes sense. Any help
would be appreciated.

Thanks,
David Shertzer
(e-mail address removed)

 

[Jeff Boyce]

David

If your "time" fields are coming across as text, consider treating them as
text, not date/time values.

It isn't clear to me yet whether you have "point-in-time" data or "duration"
data...

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Jeff,

I don't know how the DateDiff() funciton is working, but it does. And that
is what one of those things that has me puzzled.

When I returned back to my office today, I examined my data closer. Hope
this additional info might help. What I discovered is that the Sql database
table that is storing the data that contain the "time" fields is a "varchar"
datatype. When I link to that table Access2003 is interpreting the fields as
text.

I have not had an opportunity to try what you suggested yet. I still don't
have the average thing yet but I believe I need to resolve the other before I
can move on to that.

Still stump & puzzled.

It isn't clear to me yet how you can be using the DateDiff() function if the
times are text, not a date/time field.

If you subtract two numbers and get a negative number, you could use the
Abs() (absolute value) function to convert all answers to their positive
value equivalent.

If you are trying to handle a time span that crosses midnight, perhaps you
could add 12 hours (I'd need to know more about your data, but that's a
possibility) to the 'after midnight' time value.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Here is my problem and scenairo: I have used access to link to a sql
database table in hopes to do some response and average time
queries/calculations. What I soon discovered was there was a only one date
field for storing the date as a date/time, and three separate field for
storing times (received,dispatched, arrived.) However the times are being
stored as text (at least this is how Access in interpeted the fields

when
I

made my link.) Of course, I have no control over how the values are being
stored in the sql database. What I would like to do first is simply

see
the

elapse time between two times in a time format of 1.6 or 1.7 or .5 minutes.
(Taking in considerations the midnight switch. I have only a single date).
I then would like to be able to perform some averaging to see what my various
response times are. Also, having it displayed/formated as aforemention.

I have used the datediff function and have been able to get some

results
in

the format I want except I don't know how to handle the negative
values(results from the midnight changeover.) I am using the below in a
query

Result: DateDiff("s",[rcvtime],[cleartime])/60

But because of data types and how I would like it displayed I cannot manage
the averaging.

I hope I have provided enough information and that it makes sense.

Any
help

would be appreciated.

Thanks,
David Shertzer
(e-mail address removed)

 

[David S]

Jeff,

I would consider it a point in time . For example : I take a call for
service from a customer, a time is reccorder; I then dispatch the call, a
time is recorded; the driver goes enroute a time is recorded; the driver
arrives, a time is recorded and the driver clears the call, a time is
recorded. What I need to be able to do is a time analyis of this data for
such things as: Response Times, Amt of Time spent on the call, Average Time,
etc. I may need to do this on a daily, weekly, monthly, and yearly basis.

I hope this helps out.

David

If your "time" fields are coming across as text, consider treating them as
text, not date/time values.

It isn't clear to me yet whether you have "point-in-time" data or "duration"
data...

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Jeff,

I don't know how the DateDiff() funciton is working, but it does. And that
is what one of those things that has me puzzled.

When I returned back to my office today, I examined my data closer. Hope
this additional info might help. What I discovered is that the Sql database
table that is storing the data that contain the "time" fields is a "varchar"
datatype. When I link to that table Access2003 is interpreting the fields as
text.

I have not had an opportunity to try what you suggested yet. I still don't
have the average thing yet but I believe I need to resolve the other before I
can move on to that.

Still stump & puzzled.

It isn't clear to me yet how you can be using the DateDiff() function if the
times are text, not a date/time field.

If you subtract two numbers and get a negative number, you could use the
Abs() (absolute value) function to convert all answers to their positive
value equivalent.

If you are trying to handle a time span that crosses midnight, perhaps you
could add 12 hours (I'd need to know more about your data, but that's a
possibility) to the 'after midnight' time value.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Here is my problem and scenairo: I have used access to link to a sql
database table in hopes to do some response and average time
queries/calculations. What I soon discovered was there was a only one
date
field for storing the date as a date/time, and three separate field for
storing times (received,dispatched, arrived.) However the times are being
stored as text (at least this is how Access in interpeted the fields when
I
made my link.) Of course, I have no control over how the values are being
stored in the sql database. What I would like to do first is simply see
the
elapse time between two times in a time format of 1.6 or 1.7 or .5
minutes.
(Taking in considerations the midnight switch. I have only a single
date).
I then would like to be able to perform some averaging to see what my
various
response times are. Also, having it displayed/formated as aforemention.

I have used the datediff function and have been able to get some results
in
the format I want except I don't know how to handle the negative
values(results from the midnight changeover.) I am using the below in a
query

Result: DateDiff("s",[rcvtime],[cleartime])/60

But because of data types and how I would like it displayed I cannot
manage
the averaging.

I hope I have provided enough information and that it makes sense. Any
help
would be appreciated.

Thanks,
David Shertzer
(e-mail address removed)