| Cindy Winegarden 2005-07-01, 8:24 pm |
| Hi Pxriet,
*--Start by getting a count of lines by account number
Select AccountNumber, Count(*) As NumLines ;
From MainTable ;
Into Cursor Data1 ;
Group By AccountNumber
*-- Get the top 50% of those
Select Top 50 Percent Data1.* ;
From Data1 ;
Into Cursor Data2 ;
Order By NumLines
*-- Replace the account numbers
*-- where there isn't a matching record
*-- in the top group.
Select Data2
Index On Account Tag AN
Select MainTable
Set Relation To AccountNumber Into Data2
Replace All MainTable.AccountNumber;
With "Other" ;
For Not Found("Data2")
*----------
Please note, this is "air code" so if it doesn't exactly work and you can't
figure it out then post back. Also, there are some limitations to the
behavior of the Top() function in the cases where there are "tie" values.
See the Help on Select - SQL.
--
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"pxriet" <pxriet@discussions.microsoft.com> wrote in message
news:00DBE9B8-43D4-45F6-BD1A- 0DFB8AD77343@microso
ft.com...
>I have a large database that has individual customer account numbers. Each
> account will have multiple lines within the database.
> I am looking for a quick & easy way to:
>
> 1. Rank the accounts by number of lines
> 2. Find the 50 % of total and cutoff
> 3. The top 50 % keepo their account numbers, all other accounts are
> changed
> to account number = 'other'
>
> Is there an easy way to find this?
>
|