I hadn't thought of using the MAX function, very good suggestion. With a slight modification it works. It only provides the Trans ID and latest date, so I save that query as MaxofTestDates, create an INNER JOIN between that and my original parent-child tables and Voila!!!
Code:
SELECT [Transformer Test Data].[Tran ID], [Transformer List].Equipment, [Transformer List].Description, [Transformer Test Data].TestDate, [Transformer Test Data].[VPM_Oil Temperature], [Transformer Test Data].[VPM_Oil Pressure]
FROM MaxofTestDates INNER JOIN ([Transformer List] INNER JOIN [Transformer Test Data] ON [Transformer List].ID = [Transformer Test Data].[Tran ID]) ON (MaxofTestDates.[Tran ID] = [Transformer Test Data].[Tran ID]) AND (MaxofTestDates.MaxOfTestDate = [Transformer Test Data].TestDate);
and MaxofTestDates was your previous query with a minor modification.
Code:
SELECT [Transformer Test Data].[Tran ID], Max([Transformer Test Data].TestDate) AS MaxOfTestDate
FROM [Transformer List] LEFT JOIN [Transformer Test Data] ON [Transformer List].ID = [Transformer Test Data].[Tran ID]
GROUP BY [Transformer Test Data].[Tran ID];
Thanks!
Looks wierd, but works!