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]

To get the total time at work use a subquery to sum all durations for the
Agent Pin by correlating the subquery with the outer query on this column.
To get the total breaks do the same but also restrict the subquery on the AUX
Name column:

SELECT [Agent Pin], [Start Time], [AUX Name], [End Time],
DATEDIFF("s",[Start Time],[End Time]) AS Duration,
(SELECT SUM(DATEDIFF("s",[Start Time],[End Time]))
FROM [YourTable] AS T2
WHERE T2.[Agent Pin] = T1.[AgentPin])
AS [Total time at work],
(SELECT SUM(DATEDIFF("s",[Start Time],[End Time]))
FROM YourTable AS T3
WHERE T3.[Agent Pin] = T1.[AgentPin]
AND [AUX Name] LIKE "*break")
AS [Total breaks]
FROM [YourTable] AS T1;

If, as I'd imagine to be the case, the table includes data for more than one
day then you'd also need to restrict the outer query and both subqueries to
the relevant day for which you are computing the times.

If you want to show a value in seconds in the format hh:nn:ss divide the
value by the number of seconds in a day and call the Format function like so:

FORMAT([Value in Seconds]/86400,"hh:nn:ss")

Ken Sheridan
Stafford, England

 

[sund00bie]

Thanks so much for your detailed response!

i will take you advise and see what i can come up with. Your
explanation makes prefect sense!

In regards to the dates. I am provided with a new file everyday in the
same format. So once i have been able to produce the required result
for 1 days data. My next object would be to try and make it as stream
line as possible to upload a new file daily and keep a cumulative
tally so i will be able to get monthly data, but thats for another
day.

Again thanks very much for your help, i will keep you posted as to how
i go.

Cheers