cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
RTI_IT
Champion Sweeper
So we need a report that shows all users using any version of Microsoft Office Professional Edition 2003 but can't seem to figure it out. Everytime I try to put it together I can get it to show one version but not the other or vise versa. There has to be an easier way!! Any help?

Thanks,
Ed
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Userdomain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblAssetCustom.State = 1
AND tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft%'
AND tblSoftwareUni.softwareName LIKE '%2003%'
AND tblSoftwareUni.softwareName LIKE '%Office Professional%'

As an alternative to checking for "2003" in the name, you could test for
tblSoftware.softwareVersion LIKE '11.%'

View solution in original post

2 REPLIES 2
RTI_IT
Champion Sweeper
Perfect!!! Thank you very much RC62N!!
RCorbeil
Honored Sweeper II
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Userdomain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
tblAssetCustom.State = 1
AND tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft%'
AND tblSoftwareUni.softwareName LIKE '%2003%'
AND tblSoftwareUni.softwareName LIKE '%Office Professional%'

As an alternative to checking for "2003" in the name, you could test for
tblSoftware.softwareVersion LIKE '11.%'