Assigning Users to Projects

[NWAmike]

Hi. I have two tables. The first is a list of users. The second is a list of projects. I need to loop through the user list and assign each one to a project. At the end of the user list it will just start over at the top until all the projects have someone assigned to it. What is the best way to do this?

Table1 Users
User1
User2
User3

Assignments
Project Assigned To
Project1
Project2
Project3
Project4
Project5
Project6

After processing.
Project Assigned To
Project1 User1
Project2 User2
Project3 User3
Project4 User1
Project5 User2
Project6 User3

 

[RainLover]

Are you sure this is what you want to do.

When are you going to run this.

We need more information so as not to change existing associations between project and user if that is not your intention.

My thinking is that you will end up with some code that keeps looping through the tables until the conditions are met.

Some further information from you would be more than useful.

 

[NWAmike]

Thank you for your reply to my question. In this situation I am processing the data to be pushed in to a SharePoint list for the users to access. Up to this point in the process there is no relationship between the users and the projects. After I run the code to assign the users to a project and the data is loaded in SharePoint the Project list will be cleared and loaded with new projects next week. I am trying to automate a process that the users were manually doing in Excel every week.

 

[gblack]

So you just want user 1, 2, 3, to cycle against all projects so (e.g. project 17 would = User2?)

Is this what you want?

 

[Gasman]

PMFJI, I would probably start with an outside loop for projects and finish when that EOF is reached. Within that loop I would have the loop for the users. When EOF Move First and start again.

I do something similar assigning cases to users, but several cases at a time, let's say 30.

For that process I loop through the user table looking to see what type of cases are required for which users and then execute a sql statement to get that type of data, and update the records in that recordset with user details.

I continue until I have assigned the required number or the available cases have been assigned. Then I move on to the next user record.

I have a flag in the table which tells me if I need to assign cases or not, as well as the number assigned.

If you only have one user per project then this method would work, but if multiple users per project, you would be better off with a links table and create records for the assignments.

However I am an Access newbie, so there might be a more efficient or better way.?

HTH

 

[NWAmike]

So you just want user 1, 2, 3, to cycle against all projects so (e.g. project 17 would = User2?)

Is this what you want?

Yes that is what I need. I don't think it is overly complicated process. I am just not good and writing the code.


Gasman, I think you're inline with what I was thinking but I just don't know how to set it up.

 

[gblack]

I wrote a quick & dirty simple recordset example... I added a "users" column/field to the Project table and fill it using the code.

Take a look at the OnClick event procedures of the "RUN CODE" button.

HTH,
Gary

 

[NWAmike]

Wow Gblack that is exactly what I needed. Thanks so much for the help!

 

[RainLover]

Mike

It is good that you have found a solution.

When you come across this problem again have a Look at DMax plus one.

There are some samples in our archives for you to play with.