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]<[X] And [X]<[CLOCK_OUT] And [CLOCK_OUT]<[Y],([CLOCK_OUT]-[X])*24*60,0)+
     IIf([CLOCK_IN]<[X] And [Y]=[CLOCK_OUT],60,0)+
     IIf([CLOCK_IN]=[X] And [Y]<[CLOCK_OUT],60,0)+
     IIf([X]<[CLOCK_IN] And [CLOCK_IN]<[Y] And [Y]<[CLOCK_OUT],([Y]-[CLOCK_IN])*24*60,0)+
     IIf([X]<[CLOCK_IN] And [CLOCK_IN]<[CLOCK_OUT] And [CLOCK_OUT]<[Y],([Y]-   [CLOCK_IN])*24*60,0)+
     IIf([CLOCK_IN]<[X] And [X]<[Y] And [Y]<[CLOCK_OUT],60,0)+
     IIf([X]=[CLOCK_IN] And [Y]=[CLOCK_OUT],60,0)+
     IIf([X]=[CLOCK_IN] And [CLOCK_OUT]<[Y],24*60*([CLOCK_OUT]-[X]),0)+
     IIf([X]<[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])2460. 2 statements result in ([Y]-[CLOCK_IN])2460. 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 < X AND X < CLOCK_OUT AND CLOCK_OUT < Y
    OR X < CLOCK_IN AND CLOCK_OUT = Y
    OR X = CLOCK_IN AND CLOCK_OUT < Y
    )
    +
    (
    SELECT ((Y - CLOCK_IN) * 24) * 60
    FROM Your_Table_Name
    WHERE X < CLOCK_IN AND CLOCK_IN < Y AND Y < CLOCK_OUT
    OR  X < CLOCK_IN AND CLOCK_IN < CLOCK_OUT AND CLOCK_OUT < Y
    )
    +
    (
    SELECT 60
    FROM Your_Table_Name
    WHERE CLOCK_IN < X AND Y = CLOCK_OUT
    OR CLOCK_IN = X AND Y < CLOCK_OUT
    OR CLOCK_IN < X AND X < Y AND Y < CLOCK_OUT
    OR X = CLOCK_IN AND Y = CLOCK_OUT
    )
)

StackExchange Code Review Q#28993, answer score: 2