# Thread: Identifying/Grouping Based on Sum (Access 2002)

1. ## Identifying/Grouping Based on Sum (Access 2002)

I have a table of 3,900 loan records that I want to split into 6 groups with the sum of the principal balance of each group approximately the same. I know the balance needed which is easily obtained by diividing the sum of the pool by 6 but I haven't a clue on how to identify the detail for each group. Short of lengthy trial and error does anyone know of a method with which to approach this? Thanks.

Alan

2. ## Re: Identifying/Grouping Based on Sum (Access 2002)

I would start by assigning a random number to each of the loan records (in a temporary table of course), and then sort them by the random number and divide them into 6 groups on that basis, by assigning a group number (in the temporary table). Then I would compute sums for each of the groups and see if any manual tweaking was required - the odds should be pretty good that they will be close unless you have a few unusually large or small numbers that would skew a specific group. Let us know how you proceed.

3. ## Re: Identifying/Grouping Based on Sum (Access 2002)

Thanks, Wendell. I followed your instructions but I used Excel since it was easier to manipulate the groups with Excel. Here are the steps I followed:

1. Ran a query generating the poplulation of loans being grouped (4,332 total)
2. Copied the query results and pasted into an Excel sheet
3. Used the =RAND() function to assign a random no. to each loan
4. Copied all the generated random nos. and froze them using Paste Special Values
5. Sorted the entiire group by random no.
6. Assigned group no. 1 - 5 sequentially for each group of 866 records (4,332

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•