Please Help!!!!

[mambo21]

I am working on a system that would allocate the projects to volunteers. The volunteers choose 3 projects that interest them, Choice 1 being the highest priority and 3 the least. Each project should only be allocated to one volunteer and a volunteer should be allocated to the First choice unless otherwise.

Can anyone help me on the way that I could allocate the projects to volunteers.

I have attached the database I am designing and test data.

cheers

 

[yhgtbfk]

I am working on a system that would allocate the projects to volunteers. The volunteers choose 3 projects that interest them, Choice 1 being the highest priority and 3 the least. Each project should only be allocated to one volunteer and a volunteer should be allocated to the First choice unless otherwise.

Can anyone help me on the way that I could allocate the projects to volunteers.

I have attached the database I am designing and test data.

cheers

It sounds like you need visual basic coding that you can run on opening a form, or even by clicking a button.

But I am still not understanding. You might have 50 volunteers, and 20 projects...So what happens if their choice 1, 2 and 3 are taken? Do they not get any projects?

 

[mambo21]

If all 3 choices have been allocated the project could be allocated to more than one volunteer.

I have tried to use different quiries to see if It can be done this way but no luck so far. I am also trying to do it by VB but no luck with it as well because I have never used Vb before.

Any help would be really appreciated

cheers

 

[yhgtbfk]

If all 3 choices have been allocated the project could be allocated to more than one volunteer.

I have tried to use different quiries to see if It can be done this way but no luck so far. I am also trying to do it by VB but no luck with it as well because I have never used Vb before.

Any help would be really appreciated

cheers

volString = Null 'String to hold the volunteers that have been processed

docmd.setwarnings false
'Cretae a loop, we'll do our own exit condition later
Do While 1 = 1

'If the volString is not empty, we check for volunteers NOT processed
If IsNull(volString) = False Then
getvol = DLookup("volunteername", "volunteer", "volunteername NOT IN (" & volstring &

")")
'If the volString is empty, we check for the first volunteer
Else
getvol = DLookup("volunteername", "volunteer")
End If

'If we cant find any more we exit the loop
If IsNull(getvol) = True Then
Exit Do
Else

'Lets get their first priority
check1 = dlookup("priority1", "volunteer", "volunteername = '" & getVol & "'")
'Now we check if it has been taken, or if their is no priority, assume it is taken
If isnull(check1) = false then
checktaken = dlookup("assignedto", "projects", "project = '" & check1 & "'")
Else
checktaken = null
End If

'If it is not taken
If isnull(checktaken) = true then
'Add it to the table
docmd.runsql ("UPDATE projects SET assignedto = '" & getVol & "' WHERE project = '" &

check1 & "'")
Else
'we check for thier second one
check1 = dlookup("priority2", "volunteer", "volunteername = '" & getVol & "'")
'Now we check if it has been taken, or if their is no priority, assume it is taken
If isnull(check1) = false then
checktaken = dlookup("assignedto", "projects", "project = '" & check1 & "'")
Else
checktaken = null
End If

If isnull(checktaken) = true then
'Add it to the table
docmd.runsql ("UPDATE projects SET assignedto = '" & getVol & "' WHERE project = '" &

check1 & "'")
Else
'we check for their third one
check1 = dlookup("priority3", "volunteer", "volunteername = '" & getVol & "'")
'Now we check if it has been taken, or if their is no priority, assume it is taken
If isnull(check1) = false then
checktaken = dlookup("assignedto", "projects", "project = '" & check1 & "'")
Else
hecktaken = null
End If
If isnull(checktaken) = true then
'Add it to the table
docmd.runsql ("UPDATE projects SET assignedto = '" & getVol & "' WHERE project = '"

& check1 & "'")
Else
'NOTE: This is your code for what to do if ALL their priorities have been taken
End IF

'Now we add the volunteer to the list we dont process
volstring = volstring & "'" & getvol & "',"
Loop
docmd.setwarnings true

And there it is. Just plonk that in your code for onLoad, on or clicking a button.

Note: Alot of that was a cut/paste and modify job from code I have written, if it is not perfect, let me know, and I'll be happy to assist.

Also note: I have left out the code for what happens if all projects are taken, I can help you on this once the business rules are clear

note also: That you need to use your table names etc...

ANother note: THe code can be optimised with loops etc..., but I want to keep it simple and structured.

Final note: I am on holidays in 4 hours from now, and returning March 4th. But surely the other forumites will be able to help, or write "This jerk doesnt know what he's on about. This is how you do it"

 

[mambo21]

thanks for the code, it was really helpful.

I was wondering since it does not work because it doesn't have recordsets. how can i make recordsets for it?

Here is the code with new changes

Private Sub Command0_Click()

volstring = Null 'String to hold the volunteers that have been processed

DoCmd.SetWarnings False
'Cretae a loop, we'll do our own exit condition later
Do While 1 = 1

'If the volString is not empty, we check for volunteers NOT processed
If IsNull(volstring) = False Then
getVol = DLookup("Volunt_Id", "volunteer", "Volunt_Id NOT IN (" & volstring & ")")
'If the volString is empty, we check for the first volunteer
Else
getVol = DLookup("Volunt_Id", "volunteer")
End If

'If we cant find any more we exit the loop
If IsNull(getVol) = True Then
Exit Do
Else

'Lets get their first priority
check1 = DLookup("First_Choice", "tblChoice", "Volunt_Id = '" & getVol & "'")
'Now we check if it has been taken, or if their is no priority, assume it is taken
If IsNull(check1) = False Then
checktaken = DLookup("assignedto", "tblProject", "Project_Id = '" & check1 & "'")
Else
checktaken = Null
End If

'If it is not taken
If IsNull(checktaken) = True Then
'Add it to the table
DoCmd.RunSQL ("UPDATE volunteer SET assignedto = '" & getVol & "' WHERE Project_Id = '" & check1 & "'")

Else
'we check for thier second one
check1 = DLookup("Second_Choice", "tblChoice", "Volunt_Id = '" & getVol & "'")
'Now we check if it has been taken, or if their is no priority, assume it is taken
If IsNull(check1) = False Then
checktaken = DLookup("assignedto", "tblProject", "Project_Id = '" & check1 & "'")
Else
checktaken = Null
End If

If IsNull(checktaken) = True Then
'Add it to the table
DoCmd.RunSQL ("UPDATE volunteer SET assignedto = '" & getVol & "' WHERE Project_Id = '" & check1 & "'")
Else
'we check for their third one
check1 = DLookup("Third_Choice", "tblChoice", "Volunt_Id = '" & getVol & "'")
'Now we check if it has been taken, or if their is no priority, assume it is taken
If IsNull(check1) = False Then
checktaken = DLookup("assignedto", "tblProject", "Project_Id = '" & check1 & "'")
Else
checktaken = Null
End If

If IsNull(checktaken) = True Then
'Add it to the table
DoCmd.RunSQL ("UPDATE volunteer SET assignedto = '" & getVol & "' WHERE Project_Id = '" & check1 & "'")
Else
'NOTE: This is your code for what to do if ALL their priorities have been taken
End If

'Now we add the volunteer to the list we dont process
volstring = volstring & "'" & getVol & "',"
Loop
DoCmd.SetWarnings True

End Sub

have a great Easter

 

[Mile-O]

I am on holidays in 4 hours from now, and returning March 4th.

Wow! A whole year's holiday.