Report to show description blank

[Ismail]

Hello,

I have recently started to use the Custom fields page, I have been adding each computer to its designated department.

Now I run a report that tells me which computer is in which department. All is well...


What I am stuck upon is that I want to make a report that will tell me which computer has "department" in Custom fields page field as blank. This will allow me to attend to these computers and specify which department they are currently in which in turn will give me a better report.


This is the report I made which shows me which PC's have no Symantec AV installed and are in which department currently.

SELECT 
  tblComputers.Computername,
  tblComputers.LastknownIP,
  tblComputers.LastActiveScan,
  tblCompCustom.Department
FROM
  web30repNoSymantec
  INNER JOIN tblComputers ON (web30repNoSymantec.Computer = tblComputers.Computername)
  INNER JOIN tblCompCustom ON (tblComputers.Computername = tblCompCustom.Computername)

Like I said I am looking for a report that will tell me which computers have "department" in tblCompCustom as empty

[Ismail]

This works perfectly, Your cooperation is very much appreciated. 🙂

[Hemoco]

Try this one:

SELECT     TOP 100 PERCENT dbo.tblComputers.Computername, dbo.tblComputers.Domain, dbo.web30repallcompstates.Statename, 
                      dbo.tblCompCustom.Department
FROM         dbo.tblComputers INNER JOIN
                      dbo.web30repallcompstates ON dbo.tblComputers.Computername = dbo.web30repallcompstates.Computername LEFT OUTER JOIN
                      dbo.tblCompCustom ON dbo.web30repallcompstates.Computername = dbo.tblCompCustom.Computername
WHERE     (dbo.tblCompCustom.Department IS NULL) OR
                      (dbo.tblCompCustom.Department = '')
ORDER BY dbo.tblComputers.Computername

[Hemoco]

I see the problem, I'll come back to this.

[Ismail]

It gives me a blank report 😞

[Hemoco]

Bad copy/paste, should be

SELECT TOP 100 PERCENT
  dbo.tblComputers.Computername,
  dbo.tblCompCustom.Department
FROM
  dbo.tblComputers
  LEFT OUTER JOIN dbo.tblCompCustom ON (dbo.tblComputers.Computername = dbo.tblCompCustom.Computername)
WHERE
  dbo.tblCompCustom.Department = ''
ORDER BY
  dbo.tblComputers.Computername

[Ismail]

The error message is: Incorrect Syntax near "

I believe you meant:

SELECT TOP 100 PERCENT 
  dbo.tblComputers.Computername,
  dbo.tblCompCustom.Department
FROM
  dbo.tblComputers
  LEFT OUTER JOIN dbo.tblCompCustom ON (dbo.tblComputers.Computername = dbo.tblCompCustom.Computername)
WHERE
  dbo.tblCompCustom.Department = 'N'
ORDER BY
  dbo.tblComputers.Computername

When I run it I get a blank report Where it should show me a few hundred PC's that don't have any information related to department field.

[Hemoco]

try this :

SELECT     TOP 100 PERCENT dbo.tblComputers.Computername, dbo.tblCompCustom.Department
FROM         dbo.tblComputers LEFT OUTER JOIN
                      dbo.tblCompCustom ON dbo.tblComputers.Computername = dbo.tblCompCustom.Computername
WHERE     (dbo.tblCompCustom.Department = N'')
ORDER BY dbo.tblComputers.Computername