cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
DexterBrooks
Engaged Sweeper
Trying to create a report that show specific Microsoft installed Software. We are trying to report on operating systems and Office software, and a few other Microsoft programs like visual studio. We I put the report together it will pull show the same PC multiple time for the same piece of software.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.OSname,
tblSoftwareUni.softwareName As Software
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID,
tsysOS
Where (tblSoftwareUni.softwareName Like '%Microsoft Office Standard%' Or
tblSoftwareUni.softwareName Like '%Microsoft Office Professional%')
Order By tblAssets.AssetName

Result

ATL-EDI-001 williamgristick Not scanned Microsoft Office Standard 2016
ATL-EDI-001 williamgristick Win 2016 Microsoft Office Standard 2016
ATL-EDI-001 williamgristick Win 10 Microsoft Office Standard 2016
ATL-EDI-001 williamgristick Win 10 Microsoft Office Standard 2016
ATL-EDI-001 williamgristick Win 2016 Microsoft Office Standard 2016
ATL-EDI-001 williamgristick Win 10 Microsoft Office Standard 2016
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
You don't have a relationship defined between tsysOS and anything else, so your results are repeated for every record in tsysOS.
From
tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
INNER JOIN tsysOS ON tsysOS.OSCode = tblAssets.OScode

View solution in original post

1 REPLY 1
RCorbeil
Honored Sweeper II
You don't have a relationship defined between tsysOS and anything else, so your results are repeated for every record in tsysOS.
From
tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
INNER JOIN tsysOS ON tsysOS.OSCode = tblAssets.OScode