Loop through query output and assign new field value

I have a query result that sorts records from table "A" by individual value and category.  I want to loop through the recordset and assign each record successively into one of "n" groups until all records are assigned.  Assignment to a group would be made by updating a field in the query's source table (A) with a group number.   The number of groups is variable, depending on the number of records in a second table called "TRanges".

For example,
record 1 assigned to group 1
record 2 to group 2
record 3 to group 3
record 4 to group 1
record 5 to group 2 ... and so on.

In the end, I want to have a fairly even distribution of categories in each group, and each group's total value will be successively greater (which is why I sorted the records as I did with the query).  There is no guarantee that the data will contain even categories, so the outcome of category distribution doesn't have to be statistially exact, but close enough.
I'm a bit wrapped around the axle on the code.  I can do the Do While looping through the DAO recordset, but I'm having trouble managing the variables.
ErinRadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
the  format should be something like this
dim j as integer
do until rs.eof
        for j= 1 to 3
              rs.edit
              rs!fieldgroup="group" & j
              rs.update
              rs.movenext
        next
rs.movenext
loop


upload a copy of the db


0
ErinRadAuthor Commented:
Thanks, Capricorn.  I'll try it tonight.  Couple questions...

When is it best to read the value of "j"?  I can count the records in that TRanges table and save the count in j at the start of the sub?  So it needs to read "for j= 1 to countvalue"

At what point do I specify the name of the field to update?  Should I only be looping through a single field result set?
0
Hamed NasrRetired IT ProfessionalCommented:
Try this:
Table a(aID, adesc)
aID      adesc
record 1      0
record 2      0
record 3      0
record 4      0
record 5      0
record 6      0
record 7      0
record 8      0

Loop:
Private Sub cmdCategorise_Click()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Select aID, adesc from a Order by aID")
    Dim i As Integer
    rs.MoveFirst
    i = 0
    Do While Not (rs.EOF)
        i = i + 1
       
        If i Mod 4 = 0 Then
            i = i + 1
        End If
        rs.Edit
        rs("adesc") = i Mod 4
        rs.Update
        rs.MoveNext
    Loop
End Sub

Result:
aID      adesc
record 1      group 1
record 2      group 2
record 3      group 3
record 4      group 1
record 5      group 2
record 6      group 3
record 7      group 1
record 8      group 2
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
ErinRad,

i'll wait for your sample db so we can discuss as we see the records
0
Patrick MatthewsCommented:
There is absolutely no need for vba code on this. It can be done with sql alone.

What is your sql statement?
0
ErinRadAuthor Commented:
I have to step out for an hour to run a kid to an activity.  I'll upload a sample as soon as I get back.
Thanks!

HNASR:  can you explain the math behind using MOD with 4?  Thanks.
0
ErinRadAuthor Commented:
MatthewsPatrick:  I didn't develop an SQL statement for it yet, because I didn't think it would be the way to do it.  Please tell me what you have in mind.
0
Patrick MatthewsCommented:
You said you have a query result. If you have a query result, then you have a query. If you have a query, then you have a sql statement.

A sample file would be handy :)
0
ErinRadAuthor Commented:
Table A is the source for the sorted table "A-Sort by Value Category" (produced by a make table query just for this sample - I didn't want to have to include all the query sources in the sample db).  

TRange is the source for the count of groups the records must be dispersed across.  

A.GroupIdentifier is the field that is to be updated by the assignment.  

I've simplified the records to include only the fields involved. Sample.accdb
0
ErinRadAuthor Commented:
I tested hsasr's code and it does work for a fixed number of 3 groups.  

I would need the group count to be variable, based on the count of records in TRange.  How does the code change to accomplish that?  I haven't yet tried capricorn's code snip as I need help with the "for j = 1 to n" statement to make "n" vary the count of records in TRange.

Thanks.
0
ErinRadAuthor Commented:
Sorry about that matthewpatrick... of course that's true.  The original query is truly just the A table, plus a calculated field, sorted by value and category.  

SELECT ID, GroupIdentifier, Sequence, ValueAmt, Category
FROM [A- A Detail with Total Value]
ORDER BY ValueAmt, Category;

[A- A Detail with Total Value] is an intermediate query that calulates and adds ValueAmt to each record from A using multiple sources.  
0
Hamed NasrRetired IT ProfessionalCommented:
"the group count to be variable, based on the count of records in TRange"

Try this: passing the variable in the call for sub

Private Sub AssignGroup(groupCount As Integer)
    groupCount = groupCount + 1
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Select aID, adesc from a Order by aID")
    Dim I As Integer
    rs.MoveFirst
    I = 0
    Do While Not (rs.EOF)
        I = I + 1
       
        If I Mod groupCount = 0 Then
            I = I + 1
        End If
        rs.Edit
        rs("adesc") = "group " & I Mod groupCount
        rs.Update
        rs.MoveNext
    Loop
End Sub

Private Sub cmdCategorise_Click()
    ' assume your group count is stored in a variable iGroups
    Dim iGroups As Integer
    iGroups = 5  ' can be assigned in any suitable place
    AssignGroup (iGroups)  ' issue this command with the count of groups as a parameter.
                              'Set it in the right place.
End Sub
0
Hamed NasrRetired IT ProfessionalCommented:
Better comment: count of groups picked from table TRange

Private Sub AssignGroup(groupCount As Integer)
    groupCount = groupCount + 1
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Select aID, adesc from a Order by aID")
    Dim I As Integer
    rs.MoveFirst
    I = 0
    Do While Not (rs.EOF)
        I = I + 1
       
        If I Mod groupCount = 0 Then
            I = I + 1
        End If
        rs.Edit
        rs("adesc") = "group " & I Mod groupCount
        rs.Update
        rs.MoveNext
    Loop
End Sub
Private Sub cmdCategorise_Click()
    ' assume your group count is stored in a variable iGroups
    Dim iGroups As Integer
    iGroups = DCount("cid", "TRange")  'picks group count from table TRange
    AssignGroup (iGroups)
End Sub
0
Hamed NasrRetired IT ProfessionalCommented:
This is TRange table I created:
TRange (cid, cname) - cid can be any code
cid      cname
1      name 1
2      name 2
3      name 3
4      name 4
5      name 5

This was the result:
aID      adesc
record 1      group 1
record 2      group 2
record 3      group 3
record 4      group 4
record 5      group 5
record 6      group 1
record 7      group 2
record 8      group 3
0
Hamed NasrRetired IT ProfessionalCommented:
Comment applied to your sample database.

 Sample-2.accdb

You may need to modify it to include group codes in TRange instead of group 1, 2, 3, ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ErinRadAuthor Commented:
Thanks, hnasr.  Please give me the morning to review and test and I'll post again.
0
ErinRadAuthor Commented:
hnasr:  Thank you so much.  This accomplishes what I needed, with one small change to the order by statement, per below.  Instead of ordering by aID, I ordered by ValueAmt so I could get the increasing total value across groups as I needed.   As usual, Experts Exchange makes the world a better place :).  Thanks to all who contributed.

Private Sub A___Sort_by_Value_Category_Click()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [A - Sort by Value Category] Order by ValueAmt, Category")
    Dim iGroups As Integer
    iGroups = DCount("SequenceIDLo", "TRange")
    AssignGroup rs, iGroups
End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.