Query/Expression Help Required!

[sund00bie]

Hello, i would appreciate any help in regards to the problem i am
having.

I will try and explain it to the best of my ability (im a newbie to
Access 2000)

Here is a snipt of the information i have extracted via a query. I
have been able to work out Duration via a calculation.


"Agent Pin" "Start Time" "AUX Name" "End Time" "Duration"
"7015" "13:45:21" "After Call Work" "13:54:18" "537"
"7015" "14:30:25" "After Call Work" "14:31:03" "38"
"7015" "15:28:50" "Tea Break" "15:31:09" "139"
"7015" "15:31:31" "People Support" "15:33:13" "102"
"7015" "15:33:13" "Tea Break" "15:50:20" "1027"
"7015" "16:11:25" "After Call Work" "16:19:55" "510"
"7018" "08:00:26" "Start Up" "08:10:26" "600"
"7018" "08:58:15" "People Support" "09:03:58" "343"
"7018" "09:15:11" "Tea Break" "09:30:51" "940"
"7018" "10:28:36" "People Support" "10:29:22" "46"
"7018" "10:29:57" "People Support" "10:35:00" "303"
"7018" "12:33:38" "Meal Break" "13:03:38" "1800"
"7018" "14:00:11" "Coaching" "15:03:50" "3819"
"7018" "15:03:50" "Tea Break" "15:20:19" "989"
"7018" "15:20:19" "QOL" "17:10:05" "6586"
"7018" "17:10:05" "Wind Down" "17:13:10" "185"
"7115" "07:49:56" "Start Up" "08:00:10" "614"
"7115" "09:06:45" "People Support" "09:10:27" "222"
"7115" "10:01:29" "Tea Break" "10:15:24" "835"
"7115" "11:36:29" "After Call Work" "11:40:24" "235"
"7115" "12:01:04" "Meal Break" "12:55:59" "3295"
"7115" "14:06:28" "After Call Work" "14:08:25" "117"
"7115" "14:26:22" "Tea Break" "14:26:46" "24"
"7115" "15:56:36" "Wind Down" "16:00:07" "211"

My questions are
1) I need to work out "Total Time at Work": I can make a query that
searches "AUX Name" for Start Up & Wind Down as follows

"Time at Work"
"7018" "08:00:26" "Start Up" "08:10:26" "600"
"7018" "17:10:05" "Wind Down" "17:13:10" "185" ??????????????
"7115" "07:49:56" "Start Up" "08:00:10" "614"
"7115" "15:56:36" "Wind Down" "16:00:07" "211" ??????????????
etc etc etc etc etc.

I just cant work out how to actually get "Total Time at work" for each
and every "Agent Pin" (basically i dont know how to get Wind Down(End
Time) minus Start Up(Start time))

2) I also need to be able to get a total time of any specific "Aux
Name" I am able to produce the query to search to specific code, but
again just have no idea how to get the total (i struggle because of
the two(or more) "Agent Pin" fields.
eg
"Total Breaks"
"7015" "15:28:50" "Tea Break" "15:31:09" "139"
"7015" "15:33:13" "Tea Break" "15:50:20" "1027" ??????????????
"7018" "09:15:11" "Tea Break" "09:30:51" "940"
"7018" "12:33:38" "Meal Break" "13:03:38" "1800" ??????????????
"7115" "10:01:29" "Tea Break" "10:15:24" "835"
"7115" "12:01:04" "Meal Break" "12:55:59" "3295"
"7115" "14:26:22" "Tea Break" "14:26:46" "24" ??????????????


Any help would be much appreciated

ps: i hope my crazy explanations made sense

 

[Guest]

Here's a query that I got to work, given your table name is "TimeLog":

SELECT TimeLog.AgentPIN, TimeLog.LogDate, Sum(IIf([AuxName]="Start
Up",[StartTime],0)) AS ST, Sum(IIf([AuxName]="Wind Down",[EndTime],0)) AS ET,
([ET]-[ST])*24 AS Total
FROM TimeLog
GROUP BY TimeLog.AgentPIN, TimeLog.LogDate;

Essentially it collects the start time of "Start Up" records and end time of
"Wind Down" records. I suppose you are collecting this data for more than
one day so be sure you group by a date field.

Limitations: This only allows you to collect one calculation per agent per
day and does not factor in lunch breaks, etc.

 

[Guest]

Hi

You need to use DateDiff between your start time and your end time.

Have a look at Allen Brown's excelent article on this you will find it very
helpful.

http://allenbrowne.com/casu-13.html


Allso another excelent article by John W. Vinson

http://groups.google.com/group/micr...crosoft.public.access&rnum=2#c5491dc04804cc4c

 

[sund00bie]

Thanks so much for your detailed responses.

I will use this info and try and come up with a result & will get back
to let you's know how i go. It looks pretty much like it should work,
thanks again.

My next goal is. The data i get comes in daily.csv files. Once i am
able to do these calculations, i then need to make it as stream line
as possible as. I will also need to make a cumulative total so i can
get the info at the end of the month. These questions are for another
day.

Cheers, Much appreciated.