Report with Office 2003 in all Locations

[IT-riha]

Hi,
i need a Report with the Information how many Office 2000 - 2003 (all Versions) Installations are installed on the Clients.
The Report must contain the Location of the PC and the Version.
But it will not work. 😞

Can you help me?

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Count(tblSoftware.SoftwareID) As Total,
tsysIPLocations.IPLocation,
tblAssets.IPAddress,
tsysIPLocations.StartIP,
tsysIPLocations.EndIP
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysIPLocations On tsysIPLocations.StartIP = tblAssets.IPAddress
And tsysIPLocations.EndIP = tblAssets.IPAddress
Where tblSoftwareUni.SoftwarePublisher Like '%microsoft%' And
tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
Having tblSoftwareUni.softwareName Like '%Office%2003%'
Order By Total Desc,
Software

[Daniel_B · Accepted solution]

Your join to tsysIPlocations was not quite correct. You can find an explanation of how to include the IP location in a report here. Also you need to ensure that all columns which you list in your report either have grouping enabled or are used by an aggregate function. Please find a modified report below.

Select Top 1000000 tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  Count(tblSoftware.AssetID) As Total,
  tsysIPLocations.IPLocation
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
Where (tblSoftwareUni.softwareName Like 'Microsoft office % 2003%' Or
    tblSoftwareUni.softwareName Like 'Microsoft office % 2000%') And
  tblSoftwareUni.softwareName Not Like '%viewer%' And
  tblSoftwareUni.softwareName Not Like '%tools%' And
  tblSoftwareUni.softwareName Not Like '%designer%' And tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tsysIPLocations.IPLocation
Order By Total Desc,
  Software