Display Count Range of Grouped Items

[Michael]

Hi Folks - I have a database that tracks criminal cases. For each case,
there can be multiple charges. Also, for each case, there could be the same
charge for many counts. So, the data would look something like this:

Count Charge
1 Charge1
2 Charge1
3 Charge1
4 Charge2
5 Charge2
6 Charge3
7 Charge3
8 Charge4

I'd like a report to be formatted like this:

Counts 1-3 - Charge1
Counts 4-5 - Charge2
Counts 6-7 - Charge3
Count 8 - Charge 4

Not sure where to start .... Any ideas?

Michael

 

[Guest]

This will work with your data example --
SELECT "Counts " & Min([Count]) & " - " & Max([Count]) AS Counts,
Michael.Charge
FROM Michael
GROUP BY Michael.Charge;

BUT - data does not follow the pattern you have in your example. If the data
is like this then the results will be as below --
Count Charge
1 Charge1
3 Charge1
5 Charge1
4 Charge2
2 Charge2
6 Charge3
7 Charge3
8 Charge4

Counts Charge
Counts 1 - 5 Charge1
Counts 2 - 4 Charge2
Counts 6 - 7 Charge3
Counts 8 - 8 Charge4

Also you used "Case" in explaining but not in data.

 

[Guest]

Try something like:

SELECT Charge, "Counts " & Min([Count]) & "-" & Max([Count]) AS Counts
FROM tblNoNameGiven
GROUP BY Charge;