Group a range of numbers

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

Is there a way to group a range of numbers?

Acct Number
123 3606074087
123 3606074088
123 3606074090
123 3606074091
456 3608777353
456 3608777354
456 3608777395

Result:
123 3606074087 thru 3606074088
123 3606074090 thru 3606074091
456 3608777353 thru 3608777354
456 3608777395

let me know if I'm not making any sense.

Thanks,
Keith
 
Your example IMPLIES that you are trying to group on all except the
last digit of the number.


IF IF number is a numeric field then

in your query, create a computed alias that is
the real number divided by 10 and group by that number.

IF IF number is really a txt field then

in your query, create a computed alias that is
left(realnumbername,len(realnumbername)-1) and group by that
number.


and also group by the ACCT field.


Ron
 
Hey Ron,
Sorry, there could be thousands grouped together. I have about 400k numbers
that I'm trying to group together.
I will try your suggestion but divide by a thousand and see if that works.
Thanks,
Keith
 
Okay, that's not working how I hoped.
What I was hoping was to have the first number in the range and the last
number in the range. If there is no range, then it would just display the one
number. Does that make more sense?
 
I still can't figure out what you are grouping by on the second level
of grouping.

it is definitely by the field called ACCT.

But what is the criteria for saying

123 3606074087 thru 3606074088
123 3606074090 thru 3606074091

instead of
123 3606074087 thru 3606074091

Ron
 
Divide by 1000 or whatever to get the major grouping and then put two
new fields in the query.

One of them will be the min of the real number
and the other will be the MAX of the real number.

The grouped by number does not need to be shown, but must be the
gouping agent.

Ron
 
There is a break in the numbers, there is no 3606074089.
So, I'm trying to group the numbers into blocks.

123 3606074087 thru 3606074088
123 3606074090 thru 3606074091

A company could have thousands of numbers (call center), so instead of
displaying every single number I would like to display the blocks of numbers
but there could be breaks in the block.

Keith
 
I don't think my reply came through.

Anyways, there is a break in the numbers. There is no 3606074089.

I'm trying to put the phone numbers into blocks. Customers (Call Centers)
can have a lot of numbers so instead of listing them out, I want to show them
in blocks.

Does that make any more sense?
 
OK, Your description makes sense. As to a solution, that is a
different issue that I will have to let my mind mull over a bit to see
if I can think of one.

It would not be hard at all with VBA code, but I will have to think
about a solution with a query.

I know you are trying to reduce the number of lines showing, however I
am not sure that this type of process will significantly reduce the
number of lines

You essentially have 1 to 9,999,999,999 numbers that are even more
separated by the call center separation, so adjacent numbers to
different call centers would NOT show as successive numbers. So even
if you had 10,000 calls to a center you really have only a small
selection of successive numbers. But then again area codes would
probably not be evenly spread out so some areas would have some
reduction.

I will think about it some and get back.

Ron
 
As I said, Using VBA creating a temporary table it is fairly simple.

But so far I have NOT figured out a way of getting what you want using
simply selection queries. But then again it is Friday after a long
hard week.

If someone else out there has any bright/smart ideas on how to solve
this using selection queries, please don't hesitate jumping in on the
discussion.

Maybe something will rise to the surface over the weekend.

Ron
 
Back
Top