Computer Report for Softwares Installed

[Glen]

Hello I am trying to create a report that will capture computers that DOES NOT have the specific softwares installed. The SQL script provided, still produces false information after I specified the names of the softwares. When I remove one of the statements it works but when I start to add it defaults to capturing false data.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.Department
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Adobe Acrobat%' And
tblSoftwareUni.softwareName Like '%Adobe Photoshop%') And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

[RCorbeil]

It's not weird at all. Step through the NOT IN select statement:

FOR EACH record
  IF
        the software name contains "Adobe Acrobat"
    AND the software name contains "Adobe Photoshop"
  THEN
    include the record
  ELSE
    exclude the record
  END

If it processes a record that matches Acrobat, it won't also match Photoshop, so the record is excluded from the result set. Likewise if it matches Photoshop, it won't match Acrobat.

By changing the AND to an OR,

FOR EACH record
  IF
       the software name contains "Adobe Acrobat"
    OR the software name contains "Adobe Photoshop"
  THEN
    include the record
  ELSE
    exclude the record
  END

In this case if the software contains EITHER Acrobat OR Photoshop, it's included in the result set.

Hopefully that helps shed a little light on why the change worked.

[Glen]

Thank you Charles for responding. I have solved my issue here. I am trying to capture all computers that does not have Adobe products, by adding "Where tblSoftwareUni.softwareName Like '%Adobe Arobat%' And tblSoftwareUni.softwareName Like '%Adobe Photoshop%'" It defaults the report to capture all computers instead of only the ones that does not have these products installed. How I solved it is I put an "OR" instead of the "And" weird but it worked.

[Esben_D]

Capturing false data in what way?

The only thing I can see which might be a problem is the AND in your NOT IN. Since you are stating you want all assets that do not have Acrobat AND Photoshop, but that would lead to having less results than you might want not extra false data.