GROUP/ORDER BY with multiable values in a field.

[Larshg]

Hi

I have a field that describes the severity og the row, but the same severity can be several values.

defination
A3, B2, B3, C1, C2, C3 = MEDIUM
ALL other are = HIGH

I want to count the number of MEDIUM and the number of HIGH rows pr month, I can easily count the number of times in 2 selects, but the question is if I can do it in one

tabel
ID Value date
1 1 010420042045
2 B2 010420042047
3 A1 010420042050
4 C3 010420042055
5 C2 010420042055


wanted output
Number of alarms pr severity pr month
2 HIGH
3 MEDIUM


Thanks

Larshg

 

[PHV]

Something like this ?
SELECT Count(*) As [Number of alarms],
Iif(InStr(",A3,B2,B3,C1,C2,C3,", "," & [Value] & ",") > 0, "MEDIUM", "HIGH") As Severity
FROM yourTable
GROUP BY Iif(InStr(",A3,B2,B3,C1,C2,C3,", "," & [Value] & ",") > 0, "MEDIUM", "HIGH");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244