I suggest you start a new question or afew new questions and explain in a littlemore detail.
You puzzle me with severaldetails:
1. "first step is to sort 6 or more accounts with the same address(also called over-sized) to the top of our list and export total count of records and number of groups created"
If you sort by any indey you always sort the whole table, so what are you saying here? a) what if there are less than 6? What is with the rest of the list?
If you sort by zip you sort all accounts by zip.
(If not yet comprehended, replacing zip while an index on zip+x is in effect you change processing order, indeed a separate sort field is a solution)
By sorting you don't group, you have SQL GROUP BY for grouping, which means to create 1 record per group. If you sort you only sort. Grouping is not done in the SQL meaning of the word, but of course all records with same zip come in sort order, so you can see groups of more or less records with same zip. It's not needed for grouping, you can also GROUP B without sorting.
2. next step is to group records with the same substr(zip,1,5) and export counts for each group, excluding over-sized ones
As said, you don't need to sort first.
3. group rest with substr(zip,1,3) exporting counts and groups too
There is no rest, is there?
Solutions:
1: [tt]Select Count(*) as Count, LEFT(zip,5) as zip5 From yourtable GROUP BY 2 HAVING Count(*)>=6 Into Cursor crsOversized[/tt]
2: [tt]Select Count(*) as Count, LEFT(zip,5) as zip5 From yourtable GROUP BY 2 HAVING Count(*)<6 Into Cursor crsNormal[/tt]
_Tally is our group count in both cases, or RecCount().
It will help to have an index on Left(zip,5), no index on anything else does accelerate this and sorting is not needed. That would only help you to manually do what the group by clause does.
There is nothing left over for the left 3 digits, all data is grouped by left 5 zip digits and counted either in crsOversized or crsNormal. If you now additionally want to group by LEFT(zip,3) you can do it the same way, but this will just create fewer each larger groups. Records you already grouped by LEFT(zip,5) are all in there, too, there is no "rest", the zip3 groups will aggregate all 5digit zip groups starting with the same 3 first digits.
Bye, Olaf.