title: sql query to calculate total working hour from same Login/Logout column in MS Access 2010
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/5449496/date-difference-between-two-records-in-same-table">Date difference between two records in same table</a>  </p>
  </blockquote>
  
  
  
  <p>I am new to MS Access, Know a little SQL and this is my first question here. before asking question I have searched a lot to find a suitable solution. Here's the scenario: </p>
  
  <p>I have a table  with the following structure: <br/>
  RCDID-- EmployeeID-- LogDate-- LogTime-- terminalID-- InOut<br/>
  1 -----------2001---------1/4/2012---15:39:27----iGuard3A-----IN<br/>
  2 -----------2023---------1/4/2012---15:45:27----iGuard3A-----IN<br/>
  3 -----------2001---------1/4/2012---15:47:29----iGuard3A-----Out<br/></p>
  
  <p>final requirement is to get the total working time for each employee id for every month. main problem is the login and log out time is on different records. but i already did that, i mean i wrote the sql  to have another calculated for every login logout time interval. with some other criteria my sql is as follows: <br/></p>
  
  <pre><code>SELECT AccessLog.EmployeeID, 
         AccessLog.LogDate, 
         AccessLog.TerminalID, 
         AccessLog.LogTime,   
         Format((SELECT MAX(LogTime) 
                 FROM AccessLog AS Alias  
                 WHERE Alias.LogTime &lt; AccessLog.LogTime 
                 AND Alias.EmployeeID = AccessLog.EmployeeID 
                 AND Alias.LogDate = AccessLog.LogDate 
                 AND AccessLog.TerminalID &lt;&gt; "iGuard1A" 
                 AND AccessLog.TerminalID  &lt;&gt; "iGuard1B" 
                 AND AccessLog.EmployeeID LIKE "2*"),"hh:nn:ss") AS PrevTime,
         Format((ElapsedTime([PrevTime],[LogTime])),"hh:nn:ss") AS Duration,
         AccessLog.InOut
  FROM AccessLog
  WHERE AccessLog.TerminalID &lt;&gt; "iGuard1A" 
  AND AccessLog.TerminalID &lt;&gt; "iGuard1B" 
  AND AccessLog.EmployeeID LIKE "2*" 
  AND AccessLog.InOut = "OUT"
  ORDER BY AccessLog.EmployeeID, AccessLog.LogDate, AccessLog.LogTime;
  </code></pre>
  
  <p>It turned out to be a complex query to me. but because this is the first time i m working on access, i wasn't aware about everything from the beginning. I calculated the prevtime and used it as login time for the corresponding logout time records.</p>
  
  <p>Now my goal is to calculate the total duration for each employee id. I used this following code. but it needs the group by clause which i m not sure about.</p>
  
  <pre><code>Format((Sum([Duration])-Int(Sum([Duration]))),"hh:nn:ss") AS TotalTime
  </code></pre>
  
  <p>this total time will be based on every month.</p>
  
  <p>any help will be appreciated. am i on the wrong path. should  i have used vba and report for this purpose?</p>