Subqueries

[lifo]

I am trying to add a Microsoft Office Version column where it displays the Software Name as the results but im stuck.

here is the subquery:

Case When tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Microsoft Office % 20%') Then
'installed' Else '---' End As [MS Office],

I dont want it to show whether its installed rather i want to fill the field with the software name

Ex. microsoft standard 2010

Any help would be appreciated!

[Susan_A · Accepted solution]

I've pasted a sample report below.

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,
  SubQuery.Software,
  SubQuery.Version,
  SubQuery.Publisher
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Top 1000000 tblSoftware.AssetID,
    tblSoftwareUni.softwareName As Software,
    tblSoftware.softwareVersion As Version,
    tblSoftwareUni.SoftwarePublisher As Publisher
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%microsoft%office%') SubQuery
    On SubQuery.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[lifo]

Thanks Daniel. Is there a way to show all PCs regardless of whether they have office or not? Currently your example only shows PCs that have Office.

Thanks

[Daniel_B]

You seem to have copied a part of another report which was used in another context. Have a look at this page, it provides a report which lists office installations.