Suggesting new values based on varying historical values

[Hembd]

I am having a problem with an employee roster i've created. Each employee can be assigned any of 9 jobs. The database keeps a history of the jobs employees have performed. I am attempting to have the DB suggest jobs for the current day, based on the job history. I need it to take into account jobs they have not performed within the week, or if all jobs have been performed, the one not performed within the longest span.

The form is set up, so that once I have entered the employees working for the day, I would click the button and it would loop through employees, suggesting jobs.

Basically "Mainsql" is supposed to return the last 7 jobs an employee was assigned. Then the rest of the code is supposed to check that as a recordset(rs) for the requirements and assign the new value.

It is then supposed to loop through the subform (rs2) performing the above task.

I'm going to keep working on it, but all this trial and error is giving a headache. Right now the code returns incorrect information, and never touches the code past line 31.

Maybe someone can offer some comments on the code and point me in the right direction? I'll give table information in the next post, I just don't want to make this one post too long (a bad habit of mine). Maybe someone has a database that does this kind of thing that I can examine.

Here is the code i'm using for the looping part: It looks really ugly, my apologies.

I'm sorry the code is more than 20 lines.

I'd appreciate any help I receive.

 
Set rs = DB.OpenRecordset(Mainsql)
 
hall = 1
 
rs.MoveFirst
 
Do Until rs.EOF
 
hall = 1
rs.FindFirst (Nz(rs![JobID2]) = hall)
 
If rs.NoMatch = True Then
 
Do Until rs.NoMatch = False Or hall = hallmax
 
hall = hall + 1
 
rs.FindFirst (Nz(rs![JobID2]) = hall)
 
Loop
 
Else
rs.MoveNext
 
End If
Loop
 
If rs.NoMatch = True Then
 
Forms!frm_assignments!frm_assignments_subform.Form!Combo8 = hall
 
Else
 
rs.Filter = "DateWorked <= sheetdate -6"
Set rsfilter = rs.OpenRecordset
Forms!frm_assignments!frm_assignments_subform.Form!Combo8 = rsfilter![JobID2]
rs.Close
rsfilter.Close
End If
 
rs2.MoveNext
 
Loop
 
Set rs2 = Nothing
End Sub
 

[ADezii]

The Key, as I see it, is to accurately populate a Recordset will all Jobs performed by a specific Employee by Date Descending, up to a MAXIMUM Number of Jobs as defined by a CONSTANT, as in:

Dim MyDB As DAO.Database
Dim rstJobs As DAO.Recordset
Dim strSQL As String
Dim lngEmpID As Long
Dim intNumOfJobs As Integer
Const conMAX_JOBS As Byte = 7
 
lngEmpID = 1        'Unique Employee ID
 
'Return ALL jobs performed by an Employee (Unique) listed chronologically
'by Date Descending (latest conMAX_JOBS for each Employee)
strSQL = "SELECT tblJobs.EmpID, tblJobs.JobNum, tblJobs.Date " & _
         "FROM tblJobs WHERE tblJobs.EmpID = " & lngEmpID & _
         " ORDER BY tblJobs.Date DESC;"
 
Set MyDB = CurrentDb
Set rstJobs = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
 
Debug.Print "Emp#", "Job#", "Date", "Sequence"
Debug.Print "-----------------------------------------------------"
 
With rstJobs
  'Loop through the Recordset as long as there are Records and the Number of
  'Records does not exceed the Value of the Constant conMAX_JOBS
  Do While Not .EOF And intNumOfJobs <> conMAX_JOBS
    intNumOfJobs = intNumOfJobs + 1
      'Major processing of Data will occur here
      Debug.Print ![EmpID], ![JobNum], Format$(![Date], "mm/dd/yyyy"), CStr(intNumOfJobs)
        .MoveNext
  Loop
End With
 
rstJobs.Close
Set rstJobs = Nothing

Sample OUTPUT based on Test Data:

Emp#          Job#          Date          Sequence
-----------------------------------------------------
 1             1            12/21/2011    1
 1             4            09/21/2011    2
 1             5            09/04/2011    3
 1             9            08/18/2011    4
 1             7            07/11/2011    5
 1             8            05/16/2011    6
 1             3            03/30/2011    7

[Rabbit]

Try something like this:

SELECT TOP 7 J.JobName, E.JobDate
FROM tblAvailableJobs AS J
   LEFT JOIN tblEmpJobs AS E
   ON J.JobID = E.JobID
WHERE EmpID = 1234
ORDER BY Nz(E.JobDate, #1/1/1900#) ASC;

You may have to fiddle around with first getting distinct jobs with max date for the employee first. But this is a proof of concept.

[Hembd]

ADezii and Rabbit, thank you for your responses. You guys were right, the key is in populating the recordset correctly. From what you gave me I was able to finish the project 99% of the way. However, Now there is another problem, but it seems to just be a syntax issue. Here are snippets of the new code i'm using. In strSQL2, there seems to be a problem with my formatting using the variables "intshift" and "intshiftnum". I keep thinking it is "intshiftnum" but no matter what I change I still get the syntax error when I try to run it.

From what you all gave me, the code grew pretty complex (for me), I don't think i've ever hit F8 so many times(to step through the code).

At any rate, your help is appreciated. On a side note, do you guys have any good resources either online or book form about code formatting? Everyone else makes theirs look good, while mine is a headache to look through. I'd love to make it easier for whoever has to mess with my code later.

Thank you!

Dim int1st As Integer
Dim int2nd As Integer
Dim intShift As String
Dim intShiftNum As Integer
 
int1st = rstForm![1stshift]
int2nd = rstForm![2ndshift]
 
If int1st = -1 Then
 
intShift = "1stshift"
intShiftNum = int1st
 
Else
 
    If int2nd = -1 Then
 
    intShift = "2ndshift"
    intShiftNum = int2nd
 
    End If
End If
 
  If intNumOfJobs = conMAX_JOBS And inthall < 10 Then
 
  strSQL2 = "SELECT tbl_assignments.AssociateID, tbl_assignments.JobID2, tbl_assignments.JobID1, tbl_assignments.DateWorked " & _
        "FROM tbl_assignments WHERE tbl_assignments.DateWorked = #" & sheetdate & "#" & _
        " AND tbl_assignments." & intShift & " = " & intShiftNum & _
         " ORDER BY tbl_assignments.DateWorked DESC;"
 
 
 

[ADezii]

" AND tbl_assignments." & intShift & " = " & intShiftNum &

You cannot reference Field Names in an SQL Statement as you have attempted.

[Hembd]

It would appear that I cannot, thanks for helping me see the issue. I just wanted to avoid extra if statements. I was able to circumvent the issue and complete the project.

Thank you for your help!