Hello,
I have a report that I have been running without a problem for quite some time.
It is a code that shows all computers with MS office along the install date, last changed, last known IP and a custom field called Department.
Code:Select Top 1000000 tblComputers.Computer, dbo.tblSoftware.softwareName,
dbo.tblSoftware.Installdate, dbo.tblSoftware.Lastchanged,
tblComputers.LastknownIP, tblCompCustom.Department
From dbo.tblSoftware Left Join
tblComputers On (dbo.tblSoftware.ComputerName = tblComputers.Computername)
Left Outer Join
tblCompCustom On (dbo.tblSoftware.ComputerName = tblCompCustom.Computername)
Where (dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Standard') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Professional') Or
(dbo.tblSoftware.softwareName =
'Microsoft Office XP Professional with FrontPage') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Professional') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Enterprise 2007') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Standard') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Premium') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 97, Professional Edition')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office FrontPage 2003') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2007')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office OneNote 2003') Or
(dbo.tblSoftware.softwareName =
'Microsoft Office XP Professional with FrontPage') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Access 2003 Runtime') Or
(dbo.tblSoftware.softwareName =
'Microsoft Office Access Runtime (English) 2007') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Accounting 2009') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Enterprise 2007') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office OneNote 2003') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2007')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Project Standard 2007') Or
(dbo.tblSoftware.softwareName =
'Microsoft Office Small Business Edition 2003') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Standard 2007') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2007')
Order By tblComputers.LastknownIP
Now when I add tblSerialnumber to show me the used product key on each PC that has Office I see double entries. As in I see the computer that has office installed twice on the report as in the original report only showed the correct number which is obviously 1.
this is my modified report.
Code:Select Top 1000000 tblComputers.Computer, dbo.tblSoftware.softwareName,
dbo.tblSoftware.Installdate, dbo.tblSoftware.Lastchanged,
tblComputers.LastknownIP, tblCompCustom.Department, tblSerialnumber.ProductKey
From dbo.tblSoftware Right Join
tblSerialnumber On tblSerialnumber.Computername = dbo.tblSoftware.ComputerName
Left Join
tblCompCustom On tblSerialnumber.Computername = tblCompCustom.Computername
Left Join
tblComputers On tblSerialnumber.Computername = tblComputers.Computername
Where (dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Standard') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Professional') Or
(dbo.tblSoftware.softwareName =
'Microsoft Office XP Professional with FrontPage') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Professional') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Enterprise 2007') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 2000 SR-1 Standard') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 2000 Premium') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office 97, Professional Edition')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office FrontPage 2003') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2007')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office OneNote 2003') Or
(dbo.tblSoftware.softwareName =
'Microsoft Office XP Professional with FrontPage') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Access 2003 Runtime') Or
(dbo.tblSoftware.softwareName =
'Microsoft Office Access Runtime (English) 2007') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Accounting 2009') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Enterprise 2007') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office OneNote 2003') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Project Professional 2007')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Project Standard 2007') Or
(dbo.tblSoftware.softwareName =
'Microsoft Office Small Business Edition 2003') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Standard 2007') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Visio Professional 2007')
Order By tblComputers.LastknownIP
