Report on Workstations without Owner

[hopkinsit]

This report works fine when I'm just reporting Assets with Owners but I want to report on Assets without owners so added the Null criteria but it didn't work. How can I get it to report on assets without owners?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Description,
tblBaseBoard.Manufacturer,
tblBaseBoard.Product As Motherboard,
tblProcessor.Name As Processor,
Ceiling(tblAssets.Memory / 1024) As Ram,
tblAssetUserRelations.Username,
tblAssets.Firstseen,
tblAssetCustom.PurchaseDate,
tblState.Statename,
tblOperatingsystem.Caption
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tblAssetUserRelations.Username Is Null) Or
(tblAssetUserRelations.Username = N'')
Order By tblAssets.AssetName

[Bruce_B · Accepted solution]

I've modified your report to show all Windows assets that do not have an asset-user relation. The relevant where clause I've added is the following: Where tblAssets.AssetID Not In (Select Top 1000000 tblAssetUserRelations.AssetID
From tblAssetUserRelations)

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Description,
  tblBaseBoard.Manufacturer,
  tblBaseBoard.Product As Motherboard,
  tblProcessor.Name As Processor,
  Ceiling(tblAssets.Memory / 1024) As Ram,
  tblAssets.Firstseen,
  tblAssetCustom.PurchaseDate,
  tblState.Statename,
  tblOperatingsystem.Caption
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssetUserRelations.AssetID
  From tblAssetUserRelations)
Order By tblAssets.AssetName