Alternative to sub query in MS Access
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.