Assign a name to a status within a table given a set of criteria

[stevestone2111]

Hi All.

This is my first post. I've lingered for a bit and have found some very useful information, but this issue doesn't appear to have been addressed before.



For my job I have built a system of queries and modules that assign statuses to a set of specific criteria. Now I want to assign names to these statuses based on a) # of names/# of statuses and b) # of overall names.

For example, my query will assign statuses so that when sorted look something like:

Red
Red
Red
Red
Red
Red
Blue
Blue
Blue
Purple
Purple
Purple
Purple
Purple

Say I have 3 different names to assign to Red, I want to divide red by 3 and assign an even value to each name. Same for blue and purple.

I have a table that lists out the statuses and names such that

Red-Steve
Red-John
Red-Sue
Blue-Steve
Purple-Mary
Purple-John
Purple-Sue
Purple-Bill

I have a code that will allow me to assign Steve to red for a specified number of times. However, I can't for the life of me figure how to, when the number of Steve's is exhausted, to assign to John; and then to Sue. I would like to, as much as possible, use variables to define each because I have over 40 statuses and as much as 25 names to each, across 25k records.

access code.txt

Here is the code I have. I believe I need to assign a unique value to the count, but when I try to go the long way and enter in actual names and status, I still can't get Access to read the second line of the other database that would apply "John".

Like I said, I'm new here so please let me know if you need any additional information.

Thanks

[June7]

Name is a reserved word. Should not use reserved words as field names.

The inner loop is repeatedly editing the same record in rstCountStatus.

There is no movement in the rstStatAssignment so the same name is used for every update.

Which recordset is pulling from which table?

If you want to provide db, follow instructions at bottom of my post.

[stevestone2111]

Name is a reserved word. Should not use reserved words as field names.

The inner loop is repeatedly editing the same record in rstCountStatus.

There is no movement in the rstStatAssignment so the same name is used for every update.

Which recordset is pulling from which table?

If you want to provide db, follow instructions at bottom of my post.

I am unable to provide the database as it is built throughout with confidential information. The records on the attached file with code are samples only. It doesn't actually say "Name" or "Red".

I am attempting to edit rstCountStatus!Logon with information from rstStatAssignment!Logon. The database rstCountStatus otherwise remains unchanged and is pulling data from rstStatAssignment.

rstStatAssignment lists my status codes and "logon" values and rstCountStatus only has the status values.

rstStatAssignment lists statuses and logon values as in the original post with only 1 logon per status. I need to keep these values on a separate table which can be changed with business needs.

I want to match the status from rstCountStatus!Status with the status from rstStatAssignment!Status, and feed me back the rstStatAssignment!Logon for a specified number of Statuses.

If I have the following Statuses in rstCountStatus!Status (which are prepopulated from previously run queries):

Red
Red
Red
Red
Blue
Blue
Purple
Purple
Purple
Purple
Purple
Purple

And rstStatAssignment has:

Red-Steve
Red-John
Blue-Sue
Purple-John
Purple-Sue
Purple-Mary

Then my desired result on rstCountStatus should be:

Red-Steve
Red-Steve
Red-John
Red-John
Blue-Sue
Blue-Sue
Purple-John
Purple-John
Purple-Sue
Purple-Sue
Purple-Mary
Purple-Mary

I have a column in rstCountStatus for the total number of statuses (sorted by status). So I divide that by the number of logon values on StatAssignment. So in the above example, it would give me 2 for Red and I am able to write the code that gives me 2 "Steve" values for the first 2 "Red" statuses. My problem is that I cannot figure out how to move on to the next record in rstStatAssignment that matches the status so that it will spit back a "John" value.

Thanks for your help.

[June7]

Here is something I think should work but for some reason rstCountStatus won't retrieve more than one record.

Sub AssignName()Dim db As DAO.Database
Dim rstCountStatus As DAO.Recordset
Dim rstStatAssignment As DAO.Recordset
Dim intCt As Integer, i As Integer, j As Integer
Set db = CurrentDb
Set rstStatAssignment = db.OpenRecordset("SELECT Status, EmpName FROM StatAssignment ORDER BY Status;")

Do While Not rstStatAssignment.EOF
    Set rstCountStatus = db.OpenRecordset("SELECT Status, Assign FROM CountStatus WHERE Status='" & rstStatAssignment!Status & "';")
    intCt = DCount("[Status]", "StatAssignment", "[Status] = '" & rstStatAssignment!Status & "'")
    For i = 1 To intCt
        For j = 1 To rstCountStatus.RecordCount / intCt
            rstCountStatus.Edit
            rstCountStatus!Assign = rstStatAssignment!EmpName
            rstCountStatus.Update
            rstCountStatus.MoveNext
        Next
        rstStatAssignment.MoveNext
    Next
    rstCountStatus.Close
Loop

End Sub