Group like numbers

[hispeaches]

Good Afternoon,

I have a query set up and I would like to do the following:

Group numbers
100 thru 199 in group 100
200 thru 299 in group 200
300 thru 399 in group 300

I did end up building a table which has the policy number ie: 101, 102 and
so forth and the corresponding category next to it. Can someone help me with
the best way to do this?

Many thanks in advance!

 

[ghetto_banjo]

well if it's always a 3 digit number, you could just make a new field
in your query:

GroupNumber: Left([YourFieldName], 1) & "00"

This would actually return a string of 100, 200 etc.
You could convert this back to a number if needed:

GroupNumber: Clng(Left([YourFieldName],1) & "00")


Now, if you need to account for 4 digit numbers as well, then you want
this:
GroupNumber: CLng(Left(Format([YourFieldName],"0000"),2) & "00")


Then in your report, you can setup the Sorting & Grouping to go on
this Group Number.

 

[kc-mass]

Hi

What you are really asking is to group on the first digit. If those numbers
"101", "102" etc are stored as text then set an expression in your query to
something like:

GroupDigit: Left([Group Number],1)

And group on the above.

If the numbers are stored as numbers then do this:

GroupDigit: Left(Cstr([Group Number]),1)

If the Group number can be more than 3 digits it would be slightly more
complex.


Regards

Kevin

 

[John Spencer]

If your field is a number field then you can group using a bit of arithmetic.
Assuming you want to group by 100's

The expression
([TheNumber]\100) * 100
will return
000 - 099 >> 0
100 - 199 >> 100
200 - 299 >> 200
....
3000 - 3099 >> 3000
3100 - 3199 >> 3100

If you are doing this in a report, you can use the sorting and grouping dialog
to group by the numbers. Just set the group interval to 100.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

If your field is a number field then you can group using a bit of
arithmetic.
Assuming you want to group by 100's

The expression
([TheNumber]\100) * 100
will return
000 - 099 >> 0
100 - 199 >> 100
200 - 299 >> 200
...
3000 - 3099 >> 3000
3100 - 3199 >> 3100

Isn't the Partition() function intended for this kind of thing?