Show MS Office software name, if installed

[carl_bennett]

Hi there,

I'm trying to report on an IP subnet of computers. I'm interested in including which version of Office the computer has installed (if present).
I DON'T want to exclude computers which don't have Office installed.

I can build two reports successfully then combine them, but this is inelegant.

I've used these two reports as references:
http://www.lansweeper.com/Forum/yaf_postst10447_Microsoft-Office-installations--without-components.aspx#post39285
http://www.lansweeper.com/Forum/yaf_postst10447_Microsoft-Office-installations--without-components.aspx#post39285

To produce this horrible nested Where statement, which no matter how much I bracket the statements surrounding the OR and AND, I get a list of PCs outside the subnet I'm targeting.

What is the best way of going about this situation?
I've tried:
- CASE statements on tblSoftwareUni.softwareName after SELECT
- Subquery of tblSoftwareUni.softwareName after SELECT (Lansweeper didn't allow the syntax..?)

Here is my code:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Description,
  tsysOS.OSname,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblADComputers.OU,
  tblSoftwareUni.softwareName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.softwareName Like 'Microsoft Office%' And
  tblSoftwareUni.softwareName Not Like '%viewer%' And
  tblSoftwareUni.softwareName Not Like '%proofing%' And
  tblSoftwareUni.softwareName Not Like '%visio%' And
  SubString(tblSoftwareUni.softwareName, Len(tblSoftwareUni.softwareName) - 4,
  3) = ' 20') Or
  (tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Microsoft Office%') And
  tblAssets.IPAddress Like '131.217.36.%')
Order By tblAssets.IPAddress