Include all PCs, regardless of State (acitve, non-active, etc..)

[harringg]

I'm trying to get the following report, but it only shows items where the State is "1". Am I using the wrong field to get the Active, Non-Active, Sold, ect.. I know that the "1" corresponds to Active, but with a wild-card, shouldn't it grab any value in that field?

Select Top 1000000 upgrade_tblComputers.Computername,
  upgrade_tblComputers.ComputerUnique,
  upgrade_Web40OSName.Compimage As icon,
  upgrade_Web40OSName.OSname,
  upgrade_tblComputers.SP As [Service Pack],
  upgrade_tblCompCustom.Location,
  upgrade_tblCompCustom.Department As [Research Unit],
  upgrade_tblCompCustom.Custom1 As [User Name],
  TsysChassisTypes.ChassisName As Type,
  upgrade_tblCompCustom.BarCode As [Inventory Number],
  upgrade_tblBIOS.ReleaseDate,
  upgrade_tblSystemEnclosure.SerialNumber,
  upgrade_tblCompCustom.State
From upgrade_tblComputers
  Left Outer Join upgrade_web40ProcessorCapacity
    On upgrade_tblComputers.Computername =
    upgrade_web40ProcessorCapacity.Computername
  Inner Join upgrade_web40ActiveComputers On upgrade_tblComputers.Computername =
    upgrade_web40ActiveComputers.Computername
  Left Join upgrade_web40CorrectMemory
    On upgrade_web40CorrectMemory.Computername =
    upgrade_tblComputers.Computername
  Left Join upgrade_Web40OSName On upgrade_Web40OSName.Computername =
    upgrade_tblComputers.Computername
  Left Join upgrade_tblComputerSystemProduct
    On upgrade_tblComputers.Computername =
    upgrade_tblComputerSystemProduct.Computername
  Left Join upgrade_tblCompCustom On upgrade_tblComputers.Computername =
    upgrade_tblCompCustom.Computername
  Left Join upgrade_tblBIOS On upgrade_tblComputers.Computername =
    upgrade_tblBIOS.Computername
  Left Join upgrade_tblSystemEnclosure On upgrade_tblComputers.Computername =
    upgrade_tblSystemEnclosure.Computername
  Left Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    upgrade_tblSystemEnclosure.ChassisTypes
Where upgrade_tblCompCustom.State Like '%'
Order By upgrade_tblComputers.Computername

[harringg]

Thank you. That gives me the report I'm looking for. With the filtering ability in V5 it allows to quickly view PCs with specific "states"

[harringg]

I was able to add the tblassetcustom records, but with or without that Where statement, I only get State=1 devices. I changed a Non-Active device to Active at the individual record and then it shows up on the report, but when I change it back to Non-Active, it's gone from the report.

The goal of this report is to query on upgrade_tblCompCustom.Custom1 to generate inventory reports, regardless if the PC is active or not, or is flagged with any other "state".

Below is my most recent report code:

Select Top 1000000 upgrade_tblComputers.Computername,
  upgrade_tblComputers.ComputerUnique,
  upgrade_Web40OSName.Compimage As icon,
  upgrade_Web40OSName.OSname,
  upgrade_tblComputers.SP As [Service Pack],
  upgrade_tblCompCustom.Location,
  upgrade_tblCompCustom.Department As [Research Unit],
  upgrade_tblCompCustom.Custom1 As [User Name],
  TsysChassisTypes.ChassisName As Type,
  upgrade_tblCompCustom.BarCode As [Inventory Number],
  upgrade_tblBIOS.ReleaseDate,
  upgrade_tblCompCustom.State,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model
From upgrade_tblComputers
  Left Outer Join upgrade_web40ProcessorCapacity
    On upgrade_tblComputers.Computername =
    upgrade_web40ProcessorCapacity.Computername
  Inner Join upgrade_web40ActiveComputers On upgrade_tblComputers.Computername =
    upgrade_web40ActiveComputers.Computername
  Left Join upgrade_web40CorrectMemory
    On upgrade_web40CorrectMemory.Computername =
    upgrade_tblComputers.Computername
  Left Join upgrade_Web40OSName On upgrade_Web40OSName.Computername =
    upgrade_tblComputers.Computername
  Left Join upgrade_tblComputerSystemProduct
    On upgrade_tblComputers.Computername =
    upgrade_tblComputerSystemProduct.Computername
  Left Join upgrade_tblCompCustom On upgrade_tblComputers.Computername =
    upgrade_tblCompCustom.Computername
  Left Join upgrade_tblBIOS On upgrade_tblComputers.Computername =
    upgrade_tblBIOS.Computername
  Left Join upgrade_tblSystemEnclosure On upgrade_tblComputers.Computername =
    upgrade_tblSystemEnclosure.Computername
  Left Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    upgrade_tblSystemEnclosure.ChassisTypes
  Inner Join tblAssetCustom On tblAssetCustom.CustID =
    upgrade_tblCompCustom.CustID
Order By upgrade_tblComputers.Computername

[Hemoco]

Remove the line below.

Inner Join upgrade_web40ActiveComputers On upgrade_tblComputers.Computername =
upgrade_web40ActiveComputers.Computername

[Hemoco · Accepted solution]

Leave out "Where upgrade_tblCompCustom.State Like '%'"

You need to add tblassetcustom, fields Model, Manufacturer, Serialnumber.

[harringg]

To build on that question, I would also like to include Manufacturer, Model and Serial fields as entered in the Edit Asset window of the PCs. I get a Serial Number with the existing report, but it's not the value I've manually entered.