Asset Report - Latest Comments and Custome Field

[Andy_Sismey]

Hi,

I'm trying to write a report which displays all assets with a custom-field set to "yes" but also display the latest comments, my report is either showing only records with comments (latest) or multiples of the same asset with all the comments, is this possible ?

Thanks

Andy

Code so far:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetCustom.State,
  tblAssetCustom.Warrantydate,
  tblAssetComments.Comment,
  tblAssetComments.AddedBy,
  tblAssetComments.Added,
  tblAssetCustom.Custom1 As Custom11
From tblAssets
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Full Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
  Right Join (Select tblAssetComments.AssetID,
        Max(tblAssetComments.Added) As Max
      From tblAssetComments
      Group By tblAssetComments.AssetID) SubQuery On SubQuery.Max =
    tblAssetComments.Added And SubQuery.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblAssetCustom.Custom1 = 'yes'
Order By tblAssetComments.Added