show only latest record on report

[lokiluke]

hi all,

i have a database that has a few linked tables.

tblCustomers
tblLocation
tblArea
tblAssets
tblTests

i would like to run a report that lists all the assets for a particular customer, grouped by location, then within the location group it will be further grouped by Area. then each asset will then have its tests listed below. this is all working well with no issues. what i want to do now is have only the test for each asset with the latest date listed on the report. here is a picture of what i mean:

the customer
-------the customer location
----------------the work area
-----------------------asset 1
-----------------------the latest test for asset 1
-----------------------asset 2
-----------------------the latest test for asset 2


how do i get the report to only show the record with the latest date?

i have tried SQL in the controlsource of a textbox but have been informed it wont accept SQL.

i have tried =DLookUp("[tblTests]![VisualInspection]","[tblTests]","[tblTests]![TestDate]=Max([tblTests]![TestDate])")
this still pulls multiple records but equates to an error so i think i am still not on the right track.

anyone able to help me?

cheers
luke

 

[lokiluke]

also the recordsource for the report is


SELECT tblLocation.CustomerLocation, tblArea.WorkArea, tblAssets.AssetID, tblAssets.Description, tblAssets.Brand, tblAssets.Model, tblAssets.SerialNumber, tblAssets.Class, tblTests.VisualInspection, tblTests.ElectricalInspection, tblTests.Missing, tblTests.Comments, tblTests.TagNumber, tblTests.TestDate
FROM tblLocation INNER JOIN ((tblArea INNER JOIN tblAssets ON tblArea.AreaID = tblAssets.AreaID) INNER JOIN tblTests ON tblAssets.AssetID = tblTests.AssetID) ON tblLocation.LocationID = tblArea.LocationID;

surely i could place a subquery in there somewhere?

 

[lokiluke]

i have managed to figure out a different method to do it, use a subreport that uses the record source of
SELECT TOP 1 tblTests.VisualInspection, tblTests.ElectricalInspection, tblTests.Missing, tblTests.Comments, tblTests.TagNumber, tblTests.TestDate
FROM tblTests
WHERE (tblTests.AssetID)=[tbAssetID]
ORDER BY tblTests.TestDate DESC;