Help with putting QUERY in a Range

[taimysho0]

Hello i have a query that spits out this (image attached)




how do i make it so that if the duration is between 1-5, then turn that number into a "5" and the values "number of sites" on the second column should all add together.

for example if 'Duration' values are 1,2,3, (as in pic), then combine the rows and turn duration into a "5". the number of sites it corresponds to should be 59+18+11, spitting out "88". so the row should now read "5" for Duration" and "88" for number of sites. I want to do this in counts of fives, so Duration can only be 5,10, or 15

let me know if i need to be more clear. thanks

[pbaldy]

Try a calculated field:

GroupOnMe: Switch(Duration <=5, 5, Duration <=10, 10, Duration >10, 15)

[taimysho0]

hi, i tried your method but am getting an error: pic attached

[June7]

Not an error, it is an input parameter prompt. Access can't find a field in source data called Duration so it treats the reference as a prompt. The expression you use to calculation Duration must be repeated in the GroupOnMe field expression.