duplicate results in report on file scan

[happygilmore]

I am performing two scans for the existence of a file at two different locations. Depending on the version of software installed the file can exist at location A, location B or both.

When I create a report to identify the PCs where the FileVersions.Found =true, I of course get duplicate entries for all the PCs that have the version where the file is at both locations.

I've looked at some other posts with similar questions but I'm not smart enough to apply the solution to my report. Can someone describe HOW to remove the duplicates? I want one report entry if the file exists at location A or location B.

My report looks like this:

Select Top 100000 tblAssets.AssetName,
tblAssetCustom.AssetID,
tblAssets.Username,
tblOperatingsystem.Caption,
tblFileVersions.FileVersion,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Where tblOperatingsystem.Caption Like 'Microsoft Windows 7 %' And
tblAssetCustom.State = 1 And tblFileVersions.FilePathfull Like
'%file.exe' And tblFileVersions.Found = 'true'
Order By tblAssets.AssetName

[happygilmore · Accepted solution]

OK looked at it again a day or two later and the solution was simple. Found this in another post that I somehow missed before.

You can try using a distinct before your 'Select' statement. We added an example below in the report.


Code:
Select Distinct Top 1000000 tblAssets.AssetID,