Counting records that have one word in a field but aren't identical

[PilotGav]

My first post here :-D



I have to create a query and then report for my boss which lists the number of servers and the number of workstations per customer.

The problem I'm having is that the ONLY way to isolate records which are either servers or workstations are through a field called "OSInfo". There are MANY server versions in this field, but I can filter them by either "Like '*server*'" or "Not Like '*server*'" since every version of a server OS has the word SERVER in it... and every workstation DOESN'T.

This still leaves me the issue that the query thinks each TYPE of server OSInfo is a seprate count... when I want to count ANY record with SERVER in the field, and then any record WITHOUT server in the field, and end up with:

Client	Servers	Workstations
ABC Inc.	3	45
Jebco Inc.	5	200

Is this possible? I'm also concerned that the Servers and Workstations count are both from the SAME filed.

Any help would be appreciated!!

Gavin

[PilotGav]

I DID it :-) My brain hurts but here's what I came up with using the Query designer:

SELECT dbo_vAuditMachineSummary.GroupName, IIf([dbo_vAuditMachineSummary]![OSInformation] Like '*server*','Servers','Workstations') AS Expr1, Count(dbo_vAuditMachineSummary.MachineId) AS CountOfMachineId
FROM dbo_vAuditMachineSummary
GROUP BY dbo_vAuditMachineSummary.GroupName, IIf([dbo_vAuditMachineSummary]![OSInformation] Like '*server*','Servers','Workstations');

I figured if I could somehow change the OSInfo to just be either Server or Workstation based on the criteria it would hopefully count that, and not the ACTUAL string!