Adding an "Employee ID" column to an asset report

[ABaker]

I have the below custom report that shows each computer and the software installed on them. It also shows the last logged-on user for each computer.

I want to add a column to the report that also shows the "Employee ID" for the last logged-on user of each computer. I know the "Employee ID" is in tblADusers, but I can't figure out how to join that table to the report to achieve what I'm trying to do.

My report-building knowledge is somewhat limited, so any insight would be very appreciated.

Here's my existing report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber As Serial,
tblAssets.IPAddress As [IP Address],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Lastseen,
tblAssets.Firstseen,
tsysIPLocations.IPLocation,
tblSoftwareUni.softwareName,
tsysOS.OSname,
tblSoftware.softwareVersion
From tblAssets
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetID

[RCorbeil · Accepted solution]

If you check the database documentation, you'll see that Userdomain and Username together form the primary key for tblADUsers. To link in tblADUsers, then,

LEFT JOIN tblADUsers ON tblADUsers.Userdomain=tblAssets.Userdomain AND tblADUsers.Username=tblAssets.Username

Once you've added tblADUsers to your query you can add the field(s) you need to your report.

[ABaker]

RC62N wrote:
If you check the database documentation, you'll see that Userdomain and Username together form the primary key for tblADUsers. To link in tblADUsers, then,

LEFT JOIN tblADUsers ON tblADUsers.Userdomain=tblAssets.Userdomain AND tblADUsers.Username=tblAssets.Username

Once you've added tblADUsers to your query you can add the field(s) you need to your report.

That worked perfectly, thank you. I had tried linking it by Username and Userdomain previously using the GUI, but I must have done it incorrectly. Adding your line under "From tblAssets" worked perfectly. Thanks for your quick reply!