SQL Oracle to Access

Hi All,

Anyone able to convert this Oracle SQL Query into something I can use in Access 2003? I've played with it a bit, but I have no clue how this would work in Access.

select agent,sum(datediff(minute,mintime, endtime)) as totalduration
from ( select t1.agent, t1.endtime, min(t3.starttime) as mintime
       from #tmp t1
       left join #tmp t2 on t2.agent = t1.agent and t2.starttime > t1.starttime and t2.starttime <= t1.endtime
       left join #tmp t3 on t3.agent = t1.agent and t3.starttime < t1.starttime and t3.endtime >= t1.starttime
       where t2.agent is null
       group by t1.agent, t1.endtime
     ) tnew
group by agent

Original Post Here


Thanks in advance.
Who is Participating?
reb73Connect With a Mentor Commented:
Sorry, I should have advised you on the Access Query in my previous post as well -

Create two queries as shown in the code snippet below (change Table1 to your actual Access Table name for all aliases below) -

SELECT T1.agent, T1.starttime, T1.endtime, MIN(T3.starttime) AS mintime
FROM (Table1 AS T1 LEFT JOIN Table1 AS T2 ON (T1.agent=T2.agent) AND (T2.starttime>T1.starttime) AND (T2.starttime<=T1.endtime)) LEFT JOIN Table1 AS T3 ON (T1.agent=T3.agent) AND (T3.starttime<T1.starttime) AND (T3.endtime>=T1.starttime)
GROUP BY T1.agent, T1.starttime, T1.endtime;
Then invoke it in another query as follows -
SELECT QryInner.agent, sum(datediff("n",IIF(QryInner.mintime IS NULL, QryInner.starttime, QryInner.mintime), QryInner.endtime)) AS totalduration
FROM QryInner
GROUP BY QryInner.agent;

Open in new window

kanusAuthor Commented:
Thank you again :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.