Hello,
I apologize if this is not the correct thread but would appreciate some help on finalizing my project. I am designing a workload assignment database that managers can use to assign workload to different employees (from 1 to 30) based on percentages.
Managers will have the option to assign workload by the Client's Last Name or last two digits of the SSN (from 00 to 99).
The way I have designed my db is similar for both ways but I am having problems with the SSNs. This is what I have done so far:
Created two different forms, one for the Last Name assignment and the other for the SSN.
On both forms I have placed:
A textbox named txtCountClients that counts the total records in my table Clients (ex. 32.483).
Another textbox (txtVariable) that divides the sum of all the percentages by the total number of clients.
(Example: The total number of percentage is 3000. Total Clients 32.483/3000 = 10.83)
30 textboxes (one for each employee) in which managers assign a percentage (txt1, txt2, txt3, etc).
Additional 30 textboxes which provide the estimate records assigned to each employee ([txt1]*[txtVariable])
TxtVariables is also the criteria of my queries.
Here is how I manipulate data and assign the workload via code: (select queries append the number of estimated records from table Clients to different tables, SSN1, SSN2, etc and then remove the same number of records from table Clients):
Dim qdfNew As DAO.QueryDef
Dim sqltext1 As String
sqltext1 = "SELECT TOP " & Me.Text408 & " CLIENTS.SPLIT FROM CLIENTS order by SPLIT;" ' text408 is the textbox with the Total Number of clients assigned to Employee 1 and Split is the Field with the last two digits of the SSN.
DoCmd.OpenQuery "deleteSSN1", acViewNormal
On Error Resume Next
'Table SSN1 – This creates a select TOP query that picks the criteria from
With CurrentDb
.QueryDefs.Delete ("My_SSNQuery")
Set qdfNew = .CreateQueryDef("My_SSNQuery", sqltext1)
.Close
End With
DoCmd.OpenQuery ("My_SSNQuery")
DoCmd.OpenQuery ("AppendSSN1")
DoCmd.OpenQuery "deleteMy_SSNQuery", acViewNormal
Me.txtFrom1 = DMin("[SPLIT]", "SSN1") ‘ textbox that show the first range of SSN – example 00
Me.txtTo1 = DMax("[SPLIT]", "SSN1") ‘textbox that show the last range of SSN – example 13
End if
Docmd.close
Obviously, I repeat the above code 30 times with the appropriate changes.
The problem I am having is with the last two digits of the SSN as when I assign the standard percentages (100%) to over 25 employees, the last ones (from 26 to 30) don’t get an assignment as all the records have already been assigned and removed from my main Clients table. To get around the problem, I have to reduce the percentage but this is not what I need. The problem obviously depends on the fact that I consequently remove the assigned records from the table Clients and left without any more to assign.
I will appreciate your help and if you wish I can post a sample database. Thank you
I apologize if this is not the correct thread but would appreciate some help on finalizing my project. I am designing a workload assignment database that managers can use to assign workload to different employees (from 1 to 30) based on percentages.
Managers will have the option to assign workload by the Client's Last Name or last two digits of the SSN (from 00 to 99).
The way I have designed my db is similar for both ways but I am having problems with the SSNs. This is what I have done so far:
Created two different forms, one for the Last Name assignment and the other for the SSN.
On both forms I have placed:
A textbox named txtCountClients that counts the total records in my table Clients (ex. 32.483).
Another textbox (txtVariable) that divides the sum of all the percentages by the total number of clients.
(Example: The total number of percentage is 3000. Total Clients 32.483/3000 = 10.83)
30 textboxes (one for each employee) in which managers assign a percentage (txt1, txt2, txt3, etc).
Additional 30 textboxes which provide the estimate records assigned to each employee ([txt1]*[txtVariable])
TxtVariables is also the criteria of my queries.
Here is how I manipulate data and assign the workload via code: (select queries append the number of estimated records from table Clients to different tables, SSN1, SSN2, etc and then remove the same number of records from table Clients):
Dim qdfNew As DAO.QueryDef
Dim sqltext1 As String
sqltext1 = "SELECT TOP " & Me.Text408 & " CLIENTS.SPLIT FROM CLIENTS order by SPLIT;" ' text408 is the textbox with the Total Number of clients assigned to Employee 1 and Split is the Field with the last two digits of the SSN.
DoCmd.OpenQuery "deleteSSN1", acViewNormal
On Error Resume Next
'Table SSN1 – This creates a select TOP query that picks the criteria from
With CurrentDb
.QueryDefs.Delete ("My_SSNQuery")
Set qdfNew = .CreateQueryDef("My_SSNQuery", sqltext1)
.Close
End With
DoCmd.OpenQuery ("My_SSNQuery")
DoCmd.OpenQuery ("AppendSSN1")
DoCmd.OpenQuery "deleteMy_SSNQuery", acViewNormal
Me.txtFrom1 = DMin("[SPLIT]", "SSN1") ‘ textbox that show the first range of SSN – example 00
Me.txtTo1 = DMax("[SPLIT]", "SSN1") ‘textbox that show the last range of SSN – example 13
End if
Docmd.close
Obviously, I repeat the above code 30 times with the appropriate changes.
The problem I am having is with the last two digits of the SSN as when I assign the standard percentages (100%) to over 25 employees, the last ones (from 26 to 30) don’t get an assignment as all the records have already been assigned and removed from my main Clients table. To get around the problem, I have to reduce the percentage but this is not what I need. The problem obviously depends on the fact that I consequently remove the assigned records from the table Clients and left without any more to assign.
I will appreciate your help and if you wish I can post a sample database. Thank you
