Subquery to return 1 result so results remain distinct

Question

I have this SQL:

SELECT a.ID,
       a.title,
       a.section,
       a.name,
       l.User AS CreatedBy,
       IIf(IsNull(l.Time),
             Null, DateAdd ( "s", l.time, #03/01/1980# )) AS CreatedAt
FROM (
       Reports AS a
       LEFT JOIN AuditLog AS l ON a.ID = l.ID
     ) 
     LEFT JOIN 
      (SELECT TOP 1 Min([time]) AS Mintime,
       URN FROM AuditLog GROUP BY ID)
      AS t ON (l.time = t.mintime) AND (l.ID = t.ID)
WHERE a.Active = 'Y';

The query works, but returns duplicate records (because there are multiple auditlogs for 1 Report). I would only like this to return one ID record showing the earliest audit time (thus what mintime is). I added the TOP 1 but that did not seem to change anything. I've also considered GROUP BY ID but a bit confused about how to still show title etc, since it complains about them not being aggregate.

Current output:

ID         title        section     CreatedBy     CreatedAt
Z.test1    Example 1    Ex          User1         01/01/2012
Z.test5    Example 5    Ex          User2         02/03/2012
Z.test6    Example 6    Ex          User3         03/06/2012
Z.test6    Example 6    Ex          User4         02/01/2000

Expected output:

ID         title        section     CreatedBy     CreatedAt
Z.test1    Example 1    Ex          User1         01/01/2012
Z.test5    Example 5    Ex          User2         02/03/2012
Z.test6    Example 6    Ex          User4         02/01/2000

As shown above, only the earliest record for Z.test6 is shown and not all results.

Attempted new query:

SELECT a.ID, a.title, a.section, a.name, l.User AS CreatedBy,   IIf(IsNull(l.Time), Null, DateAdd ( "s", l.time, #03/01/1980# )) AS CreatedAt
FROM (
              Reports AS a 
LEFT JOIN (
                    SELECT ID, Min([time]) AS Mintime FROM AuditLog GROUP BY ID
                  )  AS t ON (l.ID = t.ID)
              LEFT JOIN AuditLog AS l ON (a.ID = l.ID AND t.mintime = l.time)
)
WHERE a.Active = 'Y'

Answer 1

Swap the join order and only select the bare-minimum required fields in the subquery 't' that you need for grouping. Something like this:

SELECT a.ID,
   a.title,
   a.section,
   a.name,
   l.User AS CreatedBy,
   IIf(IsNull(l.Time),
         Null, DateAdd ( "s", l.time, #03/01/1980# )) AS CreatedAt
FROM (
   Reports AS a
   LEFT JOIN (SELECT ID, Min([time]) AS Mintime
              FROM AuditLog GROUP BY ID) AS t 
     ON (a.ID = t.ID)
   LEFT JOIN AuditLog AS l 
     ON (t.ID = l.ID AND t.mintime = l.time)
 ) 
WHERE a.Active = 'Y';