MS-ACCESS, JOIN SQL select

Question

I'm trying to build a Database Application in MS-ACCESS 2013. In this application I need a query that shows al Entries in NotificationsOfDelay joined with the last NotificationOfDelayStatus for that Item. Last means the biggest/latest timestamp. I've tried several googled SQL queries, but I just can't seem to get it right. Below my Code

SELECT tblNotificationOfDelay.ID,  tblNotificationOfDelayStatus.NotificationOfDelayID
FROM tblNotificationOfDelay 
INNER JOIN
(
SELECT TOP 1 tblNotificationOfDelayStatus.ID
FROM tblNotificationOfDelayStatus
WHERE tblNotificationOfDelayStatus.NotificationOfDelayID = tblNotificationOfDelay.ID
ORDER BY tblNotificationOfDelayStatus.TimeStamp DESC
);

Can anyone lend a hand?

Answer 1

I would suggest a correlated subquery:

SELECT nod.ID,
       (SELECT TOP 1 nods.ID
        FROM tblNotificationOfDelayStatus as nods
        WHERE nods.NotificationOfDelayID = nod.ID
        ORDER BY nods.TimeStamp DESC
       )
FROM tblNotificationOfDelay as nod;

I'm not 100% sure that the correlation conditions are correct. But a correlated subquery seems to be what you are trying to do.