Using different data types in a query equation

[Whitney]

I would like to more accuratley calculate Availability %.

Currently I have it set up as follows:
Availability: ([Idle Time]+[ACD True Talk Time]+[Total Hold Time])/([Total
Shift Time]-[Total DND Time])

I need to subtract a few other metrics from [Total Shift Time], such as
[Training]+[Meeting]+[Special Project]
The problem is that those fields are number data types, where as the other
fields are date/time data types.

Unfortunately it's not the best way to gather and store the data, but I'm
limited with the phone system, so I'm have to make do with what data I can
get.

So my question is how can I subtract the sum of
[Training]+[Meeting]+[Special Project] from [Total Shift Time], when they are
differnt data types?

I'm not an access database expert, so please explain in the most basic
terms.

 

[John W. Vinson]

I would like to more accuratley calculate Availability %.

Currently I have it set up as follows:
Availability: ([Idle Time]+[ACD True Talk Time]+[Total Hold Time])/([Total
Shift Time]-[Total DND Time])

I need to subtract a few other metrics from [Total Shift Time], such as
[Training]+[Meeting]+[Special Project]
The problem is that those fields are number data types, where as the other
fields are date/time data types.

Unfortunately it's not the best way to gather and store the data, but I'm
limited with the phone system, so I'm have to make do with what data I can
get.

So my question is how can I subtract the sum of
[Training]+[Meeting]+[Special Project] from [Total Shift Time], when they are
differnt data types?

I'm not an access database expert, so please explain in the most basic
terms.

If [Idle time] and [ACD True Talk Time] and so on are date/time fields...
you're on the wrong track. Access Date/Times are best used for specific points
in time, rather than durations. For example, summing date/time values will
give unexpected results if the sum exceeds 24 hours - instead of 25:00 you'll
get #12/31/1899 01:00:00# as the result!

If at all possible, store the durations as Long Integer seconds or minutes,
whichever granularity is appropriate. You can use the DateDIff() function to
calculate the duration of a call given start times and end times: e.g.

Total Hold Time: DateDiff("s", [TimePutOnHold], [TimeOffHold])

If you're REALLY stuck with this, you can convert a Long Integer duration to a
time on December 30, 1899 (the base of the date/time system) by converting it
to a fraction of a day:

DateAdd("n", #12/30/1899#, [Special Project])

will take 60 miNutes ("m" is Months) in Special Project and convert it to
#01:00:00am# in a date/time field.

John W. Vinson [MVP]

 

[Marshall Barton]

I would like to more accuratley calculate Availability %.

Currently I have it set up as follows:
Availability: ([Idle Time]+[ACD True Talk Time]+[Total Hold Time])/([Total
Shift Time]-[Total DND Time])

I need to subtract a few other metrics from [Total Shift Time], such as
[Training]+[Meeting]+[Special Project]
The problem is that those fields are number data types, where as the other
fields are date/time data types.

Unfortunately it's not the best way to gather and store the data, but I'm
limited with the phone system, so I'm have to make do with what data I can
get.

So my question is how can I subtract the sum of
[Training]+[Meeting]+[Special Project] from [Total Shift Time], when they are
differnt data types?

What units are the number metrics? Minutes, seconds, or ??

Once you get that figured out, then you can convert your
durations as date/time values to a number unit based on the
structure of a date/time value where the integer part is the
number of days and the fractional part of a day. E.g. 1
hour is 1/24 and 1 minute is 1/(24*60)

If you need it, I could be more specific if you provide more
details.