Report filtering on 2 pieces of sotware

[voncollr]

I need to run a report like this on but add AVG and Atempo, so the computers displayed must have both pieces of software.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblSoftwareUni.softwareName Like '%Avg%'
Order By tblAssets.AssetName

[Karel_DS · Accepted solution]

You could display things in separate columns like with this query:

Select DISTINCT Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni_2.softwareName,
tblSoftware_2.softwareVersion
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblSoftware AS tblSoftware_2 On tblAssets.AssetID = tblSoftware_2.AssetID
Inner Join tblSoftwareUni AS tblSoftwareUni_2 On tblSoftwareUni_2.softID = tblSoftwareUni_2.SoftID
Where tblSoftwareUni_2.softwareName Like '%Avg%'
AND tblSoftwareUni.softwareName Like '%Lansweeper%'
Order By tblAssets.AssetName