Time spent by product in process represented by clockin and clock out This is the code I wrote to find out the time spent by product in process represented by clockin and clock out. Time stamps X and Y (for example, 7/23/2013 7:00 AM and 7/23/2013 8:00 AM) reflect the time period where in I am calculating the time.

X, Y, CLOCK_IN and CLOCK_OUT are all timestamps.

I wrote this code, but I am not a programmer. Is there a better way to do it? Please explain step by step.

It runs against a huge set of data. How can I reduce its runtime?

Here is a calculation I'm running in MS Access 2010:

Sum(

     IIf([CLOCK_IN]&lt;[X] And [X]&lt;[CLOCK_OUT] And [CLOCK_OUT]&lt;[Y],([CLOCK_OUT]-[X])*24*60,0)+
     IIf([CLOCK_IN]&lt;[X] And [Y]=[CLOCK_OUT],60,0)+
     IIf([CLOCK_IN]=[X] And [Y]&lt;[CLOCK_OUT],60,0)+
     IIf([X]&lt;[CLOCK_IN] And [CLOCK_IN]&lt;[Y] And [Y]&lt;[CLOCK_OUT],([Y]-[CLOCK_IN])*24*60,0)+
     IIf([X]&lt;[CLOCK_IN] And [CLOCK_IN]&lt;[CLOCK_OUT] And [CLOCK_OUT]&lt;[Y],([Y]-   [CLOCK_IN])*24*60,0)+
     IIf([CLOCK_IN]&lt;[X] And [X]&lt;[Y] And [Y]&lt;[CLOCK_OUT],60,0)+
     IIf([X]=[CLOCK_IN] And [Y]=[CLOCK_OUT],60,0)+
     IIf([X]=[CLOCK_IN] And [CLOCK_OUT]&lt;[Y],24*60*([CLOCK_OUT]-[X]),0)+
     IIf([X]&lt;[CLOCK_IN] And [CLOCK_OUT]=[Y],24*60*([CLOCK_OUT]-[X]),0))/60)

)
I'll attempt to provide something helpful, even though this is old.


To  start with, just syntax-wise, [brackets] are only required if your column name contains a reserved character, such as a space or apostrophe. It looks less cluttered if you don't use them unless needed, and I don't see any need for it in your columns. 
Line breaks and tabs make code much easier to read. I know Access is quite limited in the amount of formatting you can do and doesn't allow comments, but every bit helps. 
I notice a lot of redundancy in your Iif statements. 3 statements result in ([CLOCK_OUT]-[X])*24*60. 2 statements result in ([Y]-[CLOCK_IN])*24*60. And 4 statements all result in 60 if true. My thought is to group these together as 3 subqueries and use WHERE instead of Iif to sort out the results. 


Here is how I would write this. Note I used Your_Table_Name since your code doesn't say, make sure you replace this with the actual table name. 

SUM(
    (
    SELECT ((CLOCK_OUT - X) * 24) * 60
    FROM Your_Table_Name
    WHERE CLOCK_IN &lt; X AND X &lt; CLOCK_OUT AND CLOCK_OUT &lt; Y
    OR X &lt; CLOCK_IN AND CLOCK_OUT = Y
    OR X = CLOCK_IN AND CLOCK_OUT &lt; Y
    )
    +
    (
    SELECT ((Y - CLOCK_IN) * 24) * 60
    FROM Your_Table_Name
    WHERE X &lt; CLOCK_IN AND CLOCK_IN &lt; Y AND Y &lt; CLOCK_OUT
    OR  X &lt; CLOCK_IN AND CLOCK_IN &lt; CLOCK_OUT AND CLOCK_OUT &lt; Y
    )
    +
    (
    SELECT 60
    FROM Your_Table_Name
    WHERE CLOCK_IN &lt; X AND Y = CLOCK_OUT
    OR CLOCK_IN = X AND Y &lt; CLOCK_OUT
    OR CLOCK_IN &lt; X AND X &lt; Y AND Y &lt; CLOCK_OUT
    OR X = CLOCK_IN AND Y = CLOCK_OUT
    )
)