Report Help

[asldesktop]

Hello

I am trying to write a report but I am getting stuck. I would like a report to show computer information as well as the Microsoft Office version as well.

I have written the below report which works fine, but the report does not show the computers that do not have Microsoft Office installed at all. Is there a way to change this report so that it shows all computers, and lists the MS Office version for the PC's that have it installed?

Select Top 100000 tblcomputers.Computername, tblcomputers.ComputerUnique As [Computer Name], tblcomputers.Username As Username, tblComputerSystemProduct.Vendor As [Computer Manufacturer], tblComputerSystemProduct.Name As [Computer Model], tblComputerSystemProduct.IdentifyingNumber As [Service Tag], tblCompCustom.PurchaseDate As [Purchase Date], tblCompCustom.Warrantydate As [Warranty Expiration], tblSoftware.softwareName, tblcomputers.LastknownIP As [Last Known IP Address] From tblSoftware Inner Join tblcomputers On tblcomputers.Computername = tblSoftware.ComputerName Inner Join tblComputerSystemProduct On tblcomputers.Computername = tblComputerSystemProduct.Computername Inner Join tblCompCustom On tblcomputers.Computername = tblCompCustom.Computername Where tblSoftware.softwareName Like '%Microsoft Office%' Or tblSoftware.softwareName Like '%Visio%' Or tblSoftware.softwareName Like '%Project%' Order By tblcomputers.ComputerUnique

Many thanks in advance for any assistance.

[Hemoco · Accepted solution]

Please try the report below. (Our apologies, we misunderstood your question before.)

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique As
[Computer Name], tblComputers.Username As Username,
tblComputerSystemProduct.Vendor As [Computer Manufacturer],
tblComputerSystemProduct.Name As [Computer Model],
tblComputerSystemProduct.IdentifyingNumber As [Service Tag],
tblCompCustom.PurchaseDate As [Purchase Date], tblCompCustom.Warrantydate As
[Warranty Expiration], office.softwareName, tblComputers.LastknownIP
As [Last Known IP Address]
From tblComputers Left Join
(Select tblSoftware.softwareName, tblSoftware.ComputerName
From tblSoftware
Where (tblSoftware.softwareName Like '%Microsoft Office%') Or
(tblSoftware.softwareName Like '%Visio%') Or
(tblSoftware.softwareName Like '%Project%')) office On office.ComputerName
= tblComputers.Computername Left Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername Left Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Order By tblComputers.ComputerUnique

[asldesktop]

Hello

Many thanks for the report but unfortunatley it is still disaplying the same results.

I have some computers on the network that do not have MS Office installed. I would like to see all the computers in this report, and if they have MS Office installed then have the version displayed. If they dont have it installed it can be blank.

thanks.

[Hemoco]

Please try the report below instead.

Select Top 100000 tblComputers.Computername, tblComputers.ComputerUnique As
[Computer Name], tblComputers.Username As Username,
tblComputerSystemProduct.Vendor As [Computer
Manufacturer],
tblComputerSystemProduct.Name As [Computer Model],
tblComputerSystemProduct.IdentifyingNumber As [Service Tag],
tblCompCustom.PurchaseDate As [Purchase Date], tblCompCustom.Warrantydate As
[Warranty Expiration], tblSoftware.softwareName, tblComputers.LastknownIP As
[Last Known IP Address]
From tblSoftware Right Join
tblComputers On tblComputers.Computername = tblSoftware.ComputerName Left Join
tblComputerSystemProduct On tblComputers.Computername =
tblComputerSystemProduct.Computername Left Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Where (tblSoftware.softwareName Like '%Microsoft Office%') Or
(tblSoftware.softwareName Like '%Visio%') Or
(tblSoftware.softwareName Like '%Project%')
Order By tblComputers.ComputerUnique