By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,542 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Grouping Data Range

P: n/a
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

Mar 19 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a

<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

Mar 19 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.