Grouping Data Range

[h2lm2t]

Could anyone please take a look at this?

I have a table with 3 columns: ID, ZIP and Count as below:

Original Table
ID ZIP Count
1 00001 12
2 00002 12
3 00003 11
4 00004 11
5 00005 11
6 00007 11
7 00008 11
8 00009 11
9 01008 14
10 01009 14
11 01010 15
12 01011 18
13 01012 18
14 01013 12
15 01014 12
16 01020 12
17 01021 12
I am trying to collapse the ZIPs into consecutive ZIP ranges for each
count. My results would look like the following:
Resulting Table
ID ZIP Start ZIP End Count
1 00001 00002 12
2 00003 00009 11
3 01008 01009 14
4 01010 01010 15
5 01011 01012 18
6 01013 01021 12
I tried using the TOTAL function in the query with FIRST and LAST and
GroupBy Count, but that will result the group on range in non-
consecutive format. For example, it will shows 00001 - 01021 with
count = 12, 00003-00009 with count 11 and etc.

I have more than 40,000 of zipz and I am trying to find a more
interactive way of grouping the range with minimal manual process.

Does anyone have any suggestions?

Thanks in advance for your input.

-HL

[Larry Linson]

<h2****@gmail.comwrote in message
news:11*********************@y66g2000hsf.googlegro ups.com...

Could anyone please take a look at this?

I have a table with 3 columns: ID, ZIP and Count as below:

Original Table
ID ZIP Count
1 00001 12
2 00002 12
3 00003 11
4 00004 11
5 00005 11
6 00007 11
7 00008 11
8 00009 11
9 01008 14
10 01009 14
11 01010 15
12 01011 18
13 01012 18
14 01013 12
15 01014 12
16 01020 12
17 01021 12
I am trying to collapse the ZIPs into consecutive ZIP ranges for each
count. My results would look like the following:
Resulting Table
ID ZIP Start ZIP End Count
1 00001 00002 12
2 00003 00009 11
3 01008 01009 14
4 01010 01010 15
5 01011 01012 18
6 01013 01021 12
I tried using the TOTAL function in the query with FIRST and LAST and
GroupBy Count, but that will result the group on range in non-
consecutive format. For example, it will shows 00001 - 01021 with
count = 12, 00003-00009 with count 11 and etc.

I have more than 40,000 of zipz and I am trying to find a more
interactive way of grouping the range with minimal manual process.

Does anyone have any suggestions?

Thanks in advance for your input.

-HL