This:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssets.Username,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  ProcCapacity.CPU,
  tblAssets.Memory,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen
From tblAssets
  Inner Join (Select tblAssets.AssetID,
        Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
        Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
        Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
        Max(tblProcessor.Architecture) As Architecture,
        Max(tblProcessor.Caption) As Name,
        Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
      From tblAssets
        Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
      Group By tblAssets.AssetID) As ProcCapacity On ProcCapacity.AssetID =
    tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

Gets you this:

Lansweeper-Asset-Purchase-Priority-Report

:)

Disclaimer: I did not come up with any of this on my own, just hodge-podged stuff together from other people till it worked.