Report of Software for PC/Mac mixed network

[Casey4147]

I've been tasked with creating a report that will be similar to the PC Licensing report, for Mac software. Even better, we'd like results from both platforms tallied if possible.

On the PC side, Adobe Creative Suite is shown as Adobe Creative Suite. On the Mac, it's shown as components - PhotoShop, InDesign, Illustrator, etc.

Is there a way to filter things so that if a Mac has PhotoShop, Illustrator, InDesign, Bridge, and Acrobat, it has Creative Suite Design Standard; if it also has Premiere, it has Production Premium; if it has Dreamweaver then it has Design Web...? I'm learning SQL as I go, and this bit is beyond me.

Thanks in advance for any assistance.

[Daniel_B · Accepted solution]

This might be a bit complicated. You could try something like this:

Select tblAssets.AssetID,
  tblAssets.AssetName,
  Case When tblAssets.AssetID In (Select Top 1000000 tSoftware1.AssetID
    From (Select tblMacApplications.AssetID
      From tblSoftwareUni
        Inner Join tblMacApplications On tblSoftwareUni.SoftID =
          tblMacApplications.softid
      Where tblSoftwareUni.softwareName Like '%Photoshop%') tSoftware1
      Inner Join (Select tblMacApplications.AssetID
      From tblSoftwareUni
        Inner Join tblMacApplications On tblSoftwareUni.SoftID =
          tblMacApplications.softid
      Where tblSoftwareUni.softwareName Like '%Illustrator%') tSoftware2
        On tSoftware1.AssetID = tSoftware2.AssetID Inner Join
      (Select tblMacApplications.AssetID
      From tblSoftwareUni
        Inner Join tblMacApplications On tblSoftwareUni.SoftID =
          tblMacApplications.softid
      Where tblSoftwareUni.softwareName Like '%InDesign%') tSoftware3
        On tSoftware1.AssetID = tSoftware3.AssetID Inner Join
      (Select tblMacApplications.AssetID
      From tblSoftwareUni
        Inner Join tblMacApplications On tblSoftwareUni.SoftID =
          tblMacApplications.softid
      Where tblSoftwareUni.softwareName Like '%Bridge%') tSoftware4
        On tSoftware1.AssetID = tSoftware4.AssetID Inner Join
      (Select tblMacApplications.AssetID
      From tblSoftwareUni
        Inner Join tblMacApplications On tblSoftwareUni.SoftID =
          tblMacApplications.softid
      Where tblSoftwareUni.softwareName Like '%Acrobat%') tSoftware5
        On tSoftware1.AssetID = tSoftware5.AssetID) Then
    'Creative Suite Design Standard' End As [Installed package]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1

This will become a long script for this kind of query if you want to do it for all possible combinations. It might be much easier to do it in Excel.