Monitor Count

[kamranfalahat]

So I made the following report to be used monthly in my company for a desktop inventory:

Select Top 1000000 tblComputers.Computer, tblADComputers.Description, tblComputersystem.Manufacturer, tblComputersystem.Model, tblComputerSystemProduct.IdentifyingNumber, web40ProcessorCapacity.CPU, web40CorrectMemory.Memory, tblADComputers.OperatingSystem, tblComputers.Computername, tblMonitor.MonitorModel, tblMonitor.SerialNumber From tblComputers Inner Join tblADComputers On tblComputers.Computername = tblADComputers.Computername Inner Join tblComputersystem On tblComputers.Computername = tblComputersystem.Computername Inner Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Inner Join web40ProcessorCapacity On tblComputers.Computername = web40ProcessorCapacity.Computername Inner Join web40CorrectMemory On tblComputers.Computername = web40CorrectMemory.Computername Inner Join tblMonitor On tblComputers.Computername = tblMonitor.Computername Where tblComputersystem.Model Not Like '%Virtual%' And tblADComputers.OperatingSystem Not Like '%Server%' And tblADComputers.OU Not Like '%Armenia%' Order By tblComputers.Computer

Its perfect and it does everything that I want, except one thing. As a part of the requirement, I need to figure out to show the number of monitors connected to every machine. I tried using attributes for tblMonitors, however, I get repetitive rows as many as the number of the monitors... I gave up on that idea. Now I just want a count... If Mr. x has 3 monitors, put a 3 in a column called "whatever". I even tried something like this

Select Top 1000000 computers.Computer, (Select COUNT(*) From tblMonitor As m2 Where m2.Computername = monitors.Computername) As [# of Monitors] From tblMonitor As monitors Inner Join tblComputers As computers On
monitors.Computername = computers.Computername

But again, I get repetitive rows 😞 Help please 🙂

[kamranfalahat]

Perfect!
Thank you very much =D

[Hemoco · Accepted solution]

kamranfalahat wrote:
As a part of the requirement, I need to figure out to show the number of monitors connected to every machine.

Please try the report below.

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, Count(tblMonitor.MonitorID) As [Monitor Count]
From tblComputers Inner Join
tblMonitor On tblComputers.Computername = tblMonitor.Computername
Group By tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain
Order By tblComputers.ComputerUnique