Eureka! amazing what can happen when you take a short break
i have managed to solve it using:
maxdate is:
Code:
SELECT tblAssets.AssetID, MAX(tblTests.TestDate) AS TestDate
FROM tblAssets LEFT JOIN tblTests ON tblTests.AssetID = tblAssets.AssetID
GROUP BY tblAssets.AssetID
Code:
SELECT maxdate.AssetID, maxdate.TestDate, tblTests.TagNumber
FROM maxdate LEFT JOIN tblTests ON (tblTests.AssetID=maxdate.AssetID AND tblTests.TestDate=maxdate.TestDate)
now my question is; is this the best way to do this with two queries? or is there a single query i can use?