We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


SQL Oracle to Access

kanus asked
Medium Priority
Last Modified: 2012-05-06
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.
Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Thank you again :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.