• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

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.
0
ErinRad
Asked:
ErinRad
  • 8
  • 5
  • 2
  • +1
1 Solution
 
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
 
hnasrCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
 
hnasrCommented:
"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
 
hnasrCommented:
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
 
hnasrCommented:
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
 
hnasrCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now