Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    TN
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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.
    Wendell

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    TN
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •