Adding Custom Data to Laptop Report

[Ryan2k]

Hi,

I've created a range of reports in the past however this one has me stumped!

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Username,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  TsysChassisTypes.ChassisName,
  tblAssets.IPAddress As IP,
  tblAssets.Lastseen
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
Where tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
    TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10) And
  tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

The report shows the data that I expect, however upon adding tblADusers.Displayname it generates a report for every user that has logged onto the machine, records from 118 to over >9000.

I would like to show the username of the last user on each asset, as you can see I've customised the report to adopt to chasis types which match portable criteria.

I think that this is to do with the way that the table relationship operates but my SQL capability is still amaateur.

Any help greatly appreciated.

[Hemoco · Accepted solution]

TblADusers is still incorrectly linked to tblAssets. These tables must be linked on BOTH the Username and Userdomain fields. I.e.:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tblADusers.Displayname,
tblAssets.Lastseen,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssets.IPAddress As IP
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10) And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

[Ryan2k]

Foolishy didn't add the relationship.

Code for anyone interested:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Username,
  tblADusers.Displayname,
  tblAssets.Lastseen,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  TsysChassisTypes.ChassisName,
  tblAssets.IPAddress As IP
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
    TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10) And
  tblAssets.Assettype = -1
Order By tblAssets.AssetUnique