Grouping numbers into ranges

[sabre]

Hello



Lets assume we have the following table :

Name Surname Amount Number
John Smith 1 556
John Smith 1 558
John Smith 1 559
John Smith 1 560
John Deere 1 557

What I need to achieve is something like this :

Name Surname Amount Number Ranges
John Smith 1 556:556
John Deere 1 557:557
John Smith 3 558:560

How can I achieve this in access? I searched several forums and found some solutions with oracle databases but i can't use it here due to different functions used there which are nor present in access. Thanks in advance for any help.

[ajetrumpet]

you can do this simply with a concatenation of DMAX() and DMIN() functions...however, your result set example makes absolutely no sense.

John Smith with a 3 AND a 1 record?? and with two different ranges?? Obviously that's impossible, unless you have criteria to follow as a guide for why this stuff splits into two sets of ranges, right??

[sabre]

The criteria is simple : Group only the numbers which have a direct (+ 1) follow up, otherwise if there is no +1 following number make it a separate position
If mr smith has the numbers 556, 558,559,560, 570,571,572,573,574 Group it 556:556, 558:560, 570:574. I hope you understand what i mean.

Yeah it seems hard, and i wonder if it is possible but it should be somehow.

DMAX() and DMIN() won't work because with it i will get a result like :
Name Surname Amount Number Ranges
John Smith 4 556:560
John Deere 1 557:557

which is wrong cause Mr Smith doesn't have the number 557.
I forgot to add the Number is a unique( not auto id but unique).
I also somehow refuse to accept that it is impossible cause you can do it in seconds on paper

[ajetrumpet]

If mr smith has the numbers 556, 558,559,560, 570,571,572,573,574 Group it 556:556, 558:560, 570:574. I hope you understand what i mean.

of course I can understand it, when you write it like that. The clue is obviously the extra space between the numbers and the comma.

The problem is of course, that access doesn't organize data that way. Understand?? So how do you transpose data in a record list fashion like you have it now, so that you know where the breaks are, which are evident in the above quote??

remember, this is a program. It can't determine where your data breaks are.