Well, that didn't work out quite as expected. All it did was return only the 5 batteries stored at the site, and none of the other assets.
I entered this expression in Design view as a criteria for control, Trans_Date:
Code:
(Select Max(Assets_Trans.Trans_date) from Assets_Trans where Assets_Trans!Site_ID = Sites!ID)
That produced the following sql
Code:
SELECT Assets.Category, Assets.Make, Assets.Model, Assets_Trans.Trans_Type, Assets_Trans.Trans_Date, Assets.BarCode, Assets_Trans.Asset, Assets.Type, Assets_Trans.Custodian, Assets.Group, Sites.ID, Assets_Trans.Site_ID, Assets.ID, Assets_Trans.Asset_ID
FROM Sites INNER JOIN (Assets INNER JOIN Assets_Trans ON Assets.ID = Assets_Trans.Asset_ID) ON Sites.ID = Assets_Trans.Site_ID
WHERE (((Assets_Trans.Trans_Date)=(Select Max(Assets_Trans.Trans_date) from Assets_Trans where Assets_Trans!Site_ID = Sites!ID)))
ORDER BY Assets.Category, Assets.Make, Assets.Model, Assets_Trans.Trans_Date DESC;