MS Access – Alternative to Subquery

ms accesssubquery

I'm looking into the possibilities for writing queries in MS Access, as my client currently uses Access and would be reluctant to move away to SQL Server. I've been using the data macros (which are Access 2010 onward's implementation of triggers) in order to create an audit table of sorts. Which looks something like this:

  Modification_Log
---------------------------------------------------------------------------------------------------------------------------
 ModificationID | TableName | FieldName | RecordID | InitialValue | ModifiedValue | ModifiedDate        | ModifiedEmployee |
       1        | Employee  |  Surname  |    3     |   Bloggs     |   Blaggs      | 16/09/2014 16:26:39 |    andrew        |
       2        | Employee  |  Surname  |    3     |   Blaggs     |   Bloggs      | 16/09/2014 16:27:32 |    stu           |
       28       | Employee  | Firstname |    3     |    Joe       |     Jon       | 17/09/2014 09:15:10 |    andrew        |

Which is updated dynamically when changes are made to a record on my Employee table, which looks something like this:

      Employee
----------------------------------------------------------------
 EmployeeID | PositionID | Title | Firstname | Surname | Email |
       3    |     2      |  Mr   |    Jon    |  Bloggs | none  |
       4    |     1      |  Mr   |   Tyrion  |Lannister| none  |

What I'd like to be able to do is to write a sub query which would use Max() to pull up the most recent ModificationID (an AutoNumber) where the RecordID is 3, and have the main query joined to this listing the employee details along with the most recent "ModifiedEmployee" and "ModifiedDate", giving me a recordset something like:

 EmployeeID | PositionID | Title | Firstname | Surname | Email | ModifiedDate        | ModifiedEmployee |
       3    |     2      |  Mr   |    Jon    |  Bloggs | none  | 17/09/2014 09:15:10 |    andrew        |

I don't think that's too much to ask, but seeing as MS Access doesn't allow sub queries at the moment the way I see it, I'll have to do something programmatically to carry out multiple queries in order to extract the same data, something like:

SELECT Employee.* 
FROM Employee
WHERE Employee.EmployeeID = <<Selected Employee>>

Then

SELECT Max(Modification_Log.ModificationID) As NewestModificationID
FROM Employee INNER JOIN Modification_Log ON Employee.EmployeeID = Modification_Log.RecordID
WHERE Employee.EmployeeID = <<Selected Employee>>

Then

SELECT Modification_Log.ModifiedDate, Modification_Log.ModifiedEmployee
FROM Modification_Log
WHERE Modification_Log.ModificationID = <<NewestModificationID>>

Of course; that wouldn't be too difficult to implement but I'm just wondering whether there is a more elegant solution? I'm sure there has got to be as it seems like I'm doing an awful lot of leg work just for this relatively small task.

EDIT: Max Vernon suggested that I should be able to use sub queries via the SQL window (which is what I'd been trying to do anyway) but I'm still struggling to get it working, I've attempted to replicate what I saw a user over on Stack Overflow doing:

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

Best Answer

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.

Related Question