Hi,
I'm writing a query to count devices in a department (in a hospital). The hostname of the device is nine digits. The first digit is the type of device (T = terminal and M = mobile), the second digit for the site (W = West) and the middle three are the department (TWWAER001). I have a column that uses the mid function that grabs the 4-6 digits (AER) for each device as well as two columns that pull the T/M and the site (W).
I'm trying to do a count query to count devices by department and then if they are T or M, however, it is counting each device individually. What am I missing? I want DeptCount to count the Dept column.
Query:
-----------------------------------------------------
SELECT tblWyse.[Device Name], Left([Device Name],1) AS Kind, Mid([Device Name],2,1) AS Site, Mid([Device Name],4,3) AS Dept, tblWyse.Model, Count(Mid([Device Name],4,3)) AS DeptCount
FROM tblWyse
GROUP BY tblWyse.[Device Name], Left([Device Name],1), Mid([Device Name],2,1), Mid([Device Name],4,3), tblWyse.Model
HAVING (((Left([Device Name],1))="T") AND ((Mid([Device Name],2,1))="W") AND ((tblWyse.Model)="7012-Z10D"))
ORDER BY Mid([Device Name],4,3);
----------------------------------------------
Output:
-----------------------------------------------
-------------------
Device Name Dept Kind Site Model DeptCount TWW1CC001 1CC T W 7012-Z10D 1 TWW1CC002 1CC T W 7012-Z10D 1 TWW1CC003 1CC T W 7012-Z10D 1 TWW1CC004 1CC T W 7012-Z10D 1 MWW1CC001 1CC M W 7012-Z10D 1 MWW1CC002 1CC M W 7012-Z10D 1
Output I'd like:
Dept DeptCount 1CC 6
==================
Secondary - Output
==================
I'd like to count the device by department and split the Kind count by T/M. I can nest queries but if there's a way to do both in one query, I'd rather do that.
Dept DeptCountM DeptCountT 1CC 4 2
-----------------------------
There's more departments but just didn't list here to save some space. Please let me know if you have any further questions.
Any help would be greatly appreciated.
Thanks,
Mike