Count query on column with mid function

[Bosstone100]

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

[ranman256]

Q1:
select Dept, iIf(Kind="T",1,0) as 'Terminal', iIf(Kind="M",1,0) as 'Mobile'

Q2: sum the columns in Q1

[Welshgasman]

You are also grouping by Device Name which will be unique?
Remove that and you should get the counts you require.

[Bosstone100]

Thank guys!

So, I followed Welshgasman's advice and removed the grouping by DeviceName and then incoroprated most of ranman256's advice but changed the iIf statements to use DeptCount to grab the total for that Kind (T/M) instead of the "1", which only told me if it was non-zero.

Thank you for your help! Is there a way to mark this as resolved?

[Welshgasman]

Thank guys!

So, I followed Welshgasman's advice and removed the grouping by DeviceName and then incoroprated most of ranman256's advice but changed the iIf statements to use DeptCount to grab the total for that Kind (T/M) instead of the "1", which only told me if it was non-zero.

Thank you for your help! Is there a way to mark this as resolved?

I would have thought, just removing DeviceName would be all you need to do? You already have a T or M
Then you would get

	Dept	Kind	Site	Model	DeptCount
	1CC	T	W	7012-Z10D	4
	1CC	M	W	7012-Z10D	2

[June7]

Use dropdown Thread Tools above first post to mark as Solved. Done.