Showing all assets without a department

[JTempleton]

I tried creating a report to show all assets which have no department showing. It is not happening to all assets, but some assets are showing multiple times. Where did I go wrong in making the report? All fields show the same information.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Domain,
tsysIPLocations.IPLocation,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
As OS,
tblOperatingsystem.Caption As [OS Edition],
tblComputersystem.SystemType,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Cast(Cast(tblAssets.Memory As BigInt) / 1024 As Numeric) As
[Memory size (GB)],
tblAssets.Processor,
tblAssets.Mac As MACAddress,
tblADComputers.OU,
tblState.Statename As State,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.FQDN,
tblAssetCustom.DNSName,
tblAssetCustom.Location,
tblAssetCustom.Department,
tblAssetCustom.Custom1 As Owner,
tblAssetCustom.Custom2 As [HW or VM],
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
Case tblAssetCustom.PreventCleanup
When 0 Then 'No'
When 1 Then 'Yes'
End As PreventCleanup,
tblAssets.Scanserver,
tblAssets.Username,
tblADusers.Displayname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where tsysAssetTypes.AssetTypename Not Like 'Monitor' And
tsysAssetTypes.AssetTypename Not Like 'VOIP%' And tblState.Statename =
'active' And tblAssetCustom.Department Is Null
Order By tblAssets.AssetName

[Andy_Sismey]

Hi,

Have you tried changing your first line select to ....

Select Distinct Top 1000000 tblAssets.AssetID,