SQL Optimization – Get All Records from One Table Used in Another Without Subquery

Based on the previous question and this, the following should do what you want. As with Pieter's update it doesn't assume that the ID's are in sequential order, rather it uses the ReqTime to determine order.

UPDATE Visits R
  INNER JOIN (
    SELECT ID,
      TIME_TO_SEC(TIMEDIFF(
        (SELECT B.ReqTime
         FROM Visits B
         WHERE B.ReqTime > A.ReqTime
         ORDER BY B.ReqTime
         LIMIT 1
         )
        ,A.ReqTime)) Duration
    FROM Visits A
   ) N ON R.ID = N.ID
SET R.Duration = N.Duration
WHERE Duration IS NULL;

As @Max Vernon suggested, you can create sub queries in MS Access, so perhaps I jumped the gun on that one! I couldn't understand why my sub query wasn't working; I still don't understand fully Why this solution works but the other one didn't.

I followed these steps:

• Create sql subquery as a saved query in Access. Test. - Success
• In a new query, added the saved query, added my Modification_Log table. Test. - Success
• Added the Employee table, linked this. Test. - Success
• Added my query criteria. Test. - Success
• I finally replaced my reference to the saved query with my sub query. Test. - Success

What I end up with is this:

SELECT Employee.EmployeeID, Employee.PositionID, Employee.Title, Employee.Firstname, Employee.Surname, Employee.Email, Modification_Log.ModifiedDate, Modification_Log.ModifiedEmployee
FROM 
         (SELECT Modification_Log.RecordID, Max(Modification_Log.ModificationID) As NewestModificationID
         FROM Modification_Log
         GROUP BY Modification_Log.RecordID) As LogMax 
INNER JOIN (Employee INNER JOIN Modification_Log ON Employee.EmployeeID = Modification_Log.RecordID) ON (LogMax.NewestModificationID = Modification_Log.ModificationID) AND (LogMax.RecordID = Employee.EmployeeID)
WHERE Employee.EmployeeID=3 AND Modification_Log.TableName='Employee';

Which is almost identical apart from the fact the sub query immediately follows the FROM clause, I didn't think the order of joins would make a difference but this is MS Access we're talking about.