Double entries when running report.

[Ismail]

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.

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.

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

[Hemoco]

Hi,

You can't link tblsoftware and tblserialnumber.
These are two separate lists with nothing in common, this is what's causing the double results.