Help with a OU based report

[magson]

Hi All,

please can someone assist with this report? I dont know why but i am getting duplicate entries. can anyone see why?

Also i need to add a field that shows locally installed printers but im having no joy. Thank you


Select Top 1000000 tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Where tblADComputers.OU Like '%OU=Overgate Hospice,DC=xthis,DC=nhs,DC=uk' And
tblAssetCustom.State = 1

[RCorbeil]

On the "duplicate records" front, your final JOIN is saying "give me all the printers associated with each asset".

  Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID

You're not actually including any fields from tblPrinters in your report, but you're producing results for each of the associated printers, hence what appears to be the duplicate entries. If you don't want duplicate results, remove the JOIN to tblPrinters. If, on the other hand, you want a list of printers, add fields from tblPrinters to your report and the results will no longer look like duplicates.

As far as a field showing locally-installed printers, tblPrinters.Local should tell you that, e.g.

  tblPrinters.Caption,
  CASE
    WHEN tblPrinters.Local = 0 THEN 'No'
    WHEN tblPrinters.Local = 1 THEN 'Yes'
  END AS isLocal