input data into a rota style database table (1 Viewer)

murray83

Games Collector
Local time
Today, 16:14
Joined
Mar 31, 2017
Messages
728
question time folks

have tried ol google and lots of talk about excel and random but not access

so, here it is. can/could you input data into 5 days out of 7 randomly leaving two blank

rota table has 7 fields all text called Mon, Tue, Wed etc

i then have some data made up for colleagues in table called T_Staff which has name, preferred shift sorta thing

so query selects then i want it to input from the query into the rota table
 

bastanu

AWF VIP
Local time
Today, 09:14
Joined
Apr 13, 2010
Messages
1,401
You will need VBA code for that; I assume there are some more conditions than just randomization (use preferred shift where possible, have same number of employees each day, etc.). You could assign weights to the various conditions and then do some iterations and pick the one with the highest score.

Cheers,
Vlad
 

murray83

Games Collector
Local time
Today, 16:14
Joined
Mar 31, 2017
Messages
728
yeah there is more conditions but just want to know if you can pick random people from a pool ( as long as they are not on holiday or off for another reason

if not then stick to manual and excel and dam the moaning :)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:14
Joined
May 21, 2018
Messages
8,463
Yes you can randomly select records and assign them. You will need to provide some specifics. I actually have several working demos that do just that.
 

murray83

Games Collector
Local time
Today, 16:14
Joined
Mar 31, 2017
Messages
728
Yes you can randomly select records and assign them. You will need to provide some specifics. I actually have several working demos that do just that.

as in they are not off/holiday/sick etc

they are trained for said area

and the shift pattern is there prefer either am or pm
 

murray83

Games Collector
Local time
Today, 16:14
Joined
Mar 31, 2017
Messages
728
You will have to explain this in detail. I need understanding of all tables involved, field names, where names are pulled from where assigned. And some detail where how this is to be done. You put in a lot of constraints, but meaningless for coding without understanding the data structure. It is easy but not trivial. Maybe post your database. To give you an idea of what code is involved here is my to assign random employee to cleaning shifts.
Code:
Option Compare Database
Option Explicit
Public Function getTopX_IDs(ByVal topX As Integer) As String
 Dim strSQL As String
 Dim strIDS As String
 Dim rs As DAO.Recordset
 Dim recCount As Integer
 'Used in without repeats
  strSQL = strSQL & "SELECT Top " & topX
  strSQL = strSQL & " ID "
  strSQL = strSQL & "FROM qryAvailableWithoutRepeat "
  MsgBox strSQL
  Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
  If Not (rs.EOF And rs.BOF) Then
    rs.MoveLast
    rs.MoveFirst
  End If
  Do While Not rs.EOF
    If getTopX_IDs = "" Then
      getTopX_IDs = rs!id
    Else
      getTopX_IDs = getTopX_IDs & "," & rs!id
    End If
    rs.MoveNext
  Loop
  getTopX_IDs = getTopX_IDs
End Function
Public Sub assignCleaning(ByVal topX_IDs As String)
  Dim strSQL As String
  Dim aIDS() As String
  Dim varID As Variant
  'Used in without repeats
  aIDS = Split(topX_IDs, ",")
  For Each varID In aIDS
    strSQL = "INSERT INTO employee_picked_table (employeeID_FK,Date_Picked) VALUES (" & varID & ", #" & Date & "#)"
    CurrentDb.Execute strSQL
  Next varID
End Sub
Public Sub removeFromPlay(ByVal topX_IDs As String)
  'used in without repeats
  Dim strSQL As String
  strSQL = "UPDATE employee_table SET employee_table.inPlay = False WHERE employee_table.ID In (" & topX_IDs & ")"
  CurrentDb.Execute strSQL
End Sub
Public Sub AssignWithRepeats(topX As Integer)
 Dim strSQL As String
 strSQL = "INSERT INTO employee_picked_table ( EmployeeID_FK, Date_Picked ) "
 strSQL = strSQL & "SELECT Top " & topX
 strSQL = strSQL & " ID, date() AS dtmSelected "
 strSQL = strSQL & "FROM qryAvailableMyRandom"
 'Debug.Print strSQL
 CurrentDb.Execute strSQL
End Sub
Private Sub AssignWithRepeats2(topX As Integer)
 Dim strSQL As String
 Dim available As Integer
 Dim topXs As Collection
 Dim x As Variant
 Dim rs As DAO.Recordset
 Dim intID As Integer
 'This is using code not the Rnd function
 available = DCount("ID", "qryAvailable")
 If topX > available Then
   MsgBox "There is not " & topX & " available"
 Else
   'this returns a collection with values from
   '0 to number of (avaialble - 1)
   Set rs = CurrentDb.OpenRecordset("qryAvailable")
   rs.MoveFirst
   Set topXs = getXRandomInRange(topX, available)
   For Each x In topXs
      rs.AbsolutePosition = x
      intID = rs!id
      strSQL = "INSERT INTO employee_picked_table ( EmployeeID_FK, Date_Picked ) "
      strSQL = strSQL & "VALUES ( " & intID & "," & Date & ")"
      Debug.Print strSQL
      CurrentDb.Execute strSQL
   Next x
    rs.Close
    Set rs = Nothing
 End If
End Sub
Public Sub AssignWithoutRepeats(topX As Integer)
  Dim strIDS As String
  Dim available As Integer
  Dim assigned As Integer
  available = DCount("ID", "qryAvailable")
  MsgBox available
  If available = 0 Then
    CurrentDb.Execute "upQryMakeAllInPlay"
    available = DCount("ID", "qryAvailableWithoutRepeats")
  End If
  If IsNumeric(topX) And topX > 0 Then
    strIDS = getTopX_IDs(CInt(topX))
    If Not strIDS = "" Then
     assignCleaning (strIDS)
     removeFromPlay (strIDS)
    End If
    'if you did not have enough people to assign then
    'put all in play, then remove the just added from play and rerun
    If available < topX Then
       CurrentDb.Execute "upQryMakeAllInPlay"
       removeFromPlay (strIDS)
       strIDS = getTopX_IDs(topX - available)
       assignCleaning (strIDS)
       removeFromPlay (strIDS)
     End If
  End If
End Sub

Public Function getXRandomInRange(Xrequired As Integer, ByVal rangeTop As Integer) As Collection
  Dim x As Variant
  Dim colTemp As New Collection
  Dim tempX As Integer
  Dim inCollection As Boolean
  Dim intCount As Integer
  Randomize
  Do Until intCount = Xrequired
    inCollection = False
    tempX = Fix(Rnd() * rangeTop)
    For Each x In colTemp
      If x = tempX Then
        inCollection = True
        Exit For
      End If
    Next x
    If Not inCollection Then
      colTemp.Add (tempX)
      intCount = intCount + 1
    End If
  Loop
  Set getXRandomInRange = colTemp
End Function
Public Function myRnd(id As Variant) As Double
  If Not IsNull(id) Then
    Randomize
    myRnd = Rnd(Now() + id)
  End If
End Function

Public Sub test20()
  Dim I As Integer
  For I = 1 To 20
    AssignWithRepeats (5)
  Next I
End Sub

As you can see there are a lot of rules for assigning. You can have random names, but allow repeats. Or you can randomly assign everyone, but once assigned they get pulled from the list.

cheers for that, ok well here is my start ( not much no laughing at the back )

so the main rota would be populated by manager and then the weekly rota would be run via magic ( sql and vba ) pulled from the mainrota

i so far have seen a few things that have hurt my head

inner join on colleague as the colleagues in staff are under colleague but for the main rota they are individually :banghead: as was looking at join and where the date = today and that the shift is not = to either H, HDH, S, D, Off, M, P
 

Attachments

  • Rota .mdb
    516 KB · Views: 71

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:14
Joined
May 21, 2018
Messages
8,463
inner join on colleague as the colleagues in staff are under colleague but for the main rota they are individually
The problem is that main table is not a database table but an non-normal Excel like spread sheet. It is pretty worthless for doing any database like things. You can likely make a display like that using a cross tab query. These are somewhat tough databases to design because the table structure does not look anything like the display and input. Takes a little design skills to make a good user interface. If you have not done many databases before you end up making your tables like a spreadsheet and it is easy to build but very hard and cumbersome to do anything.

The T_MainRota should be

MainRota_ID 'PK autonumber
RotationDate 'Date field
StaffID_FK ' A foreign key relating back to the staff table
RotationID_FK 'A Foreign key relating back to T_RotaCode (It used to be called T_Reasons but you will add in AM and PM to it. I would also make this field the PK instead of the autonumber ).

I am guessing the weekly Rota would have the exact same fields as the T_MainRota.
so the main rota would be populated by manager and then the weekly rota would be run via magic ( sql and vba ) pulled from the mainrota

I do not really understand what gets assigned. T_MainRota has all the assignments already or the code why they are not assigned. I am guessing the ones with shifts (0700-1500 or 1500-2300) are the potentially available? Do if I was doing a week assignment for today would I randomly pick one person from those available for the AM shift and one person from those available for the PM shift?

For today I could pick Paulina Z form AM and Christina F for PM?
 

murray83

Games Collector
Local time
Today, 16:14
Joined
Mar 31, 2017
Messages
728
The problem is that main table is not a database table but an non-normal Excel like spread sheet. It is pretty worthless for doing any database like things. You can likely make a display like that using a cross tab query. These are somewhat tough databases to design because the table structure does not look anything like the display and input. Takes a little design skills to make a good user interface. If you have not done many databases before you end up making your tables like a spreadsheet and it is easy to build but very hard and cumbersome to do anything.

The T_MainRota should be

MainRota_ID 'PK autonumber
RotationDate 'Date field
StaffID_FK ' A foreign key relating back to the staff table
RotationID_FK 'A Foreign key relating back to T_RotaCode (It used to be called T_Reasons but you will add in AM and PM to it. I would also make this field the PK instead of the autonumber ).

I am guessing the weekly Rota would have the exact same fields as the T_MainRota.


I do not really understand what gets assigned. T_MainRota has all the assignments already or the code why they are not assigned. I am guessing the ones with shifts (0700-1500 or 1500-2300) are the potentially available? Do if I was doing a week assignment for today would I randomly pick one person from those available for the AM shift and one person from those available for the PM shift?

For today I could pick Paulina Z form AM and Christina F for PM?

yeah had seen a cross tab query and ran ( may have another look as not my first database )

and yeah correct you could pick those two and the main rota has, as teh name suggests the main rota and then they would be assigned on skill base to specific area so thats the bit in the staff table which says MTM, GIN etc
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:14
Joined
May 21, 2018
Messages
8,463
Is the T_MainRota real data or notional? In other words can you start from scratch. If you want to normalize it, you can probably automate it in excel or write code. It looks like to much work to do manually. Even writing queries to fix it would be to lengthy. If you want I can do it in code pretty quick if that data is good.

and yeah correct you could pick those two and the main rota has, as teh name suggests the main rota and then they would be assigned on skill base to specific area so thats the bit in the staff table which says MTM, GIN et
So lets say you determine all the people available for the AM shift for today. Then what would you like to happen (in detail)? I assume you randomly pick one of them and then do something based on their dept. On that note if you have a Dept field why do you need boolean fields for MTM, GIN..?
 

murray83

Games Collector
Local time
Today, 16:14
Joined
Mar 31, 2017
Messages
728
Is the T_MainRota real data or notional? In other words can you start from scratch. If you want to normalize it, you can probably automate it in excel or write code. It looks like to much work to do manually. Even writing queries to fix it would be to lengthy. If you want I can do it in code pretty quick if that data is good.


So lets say you determine all the people available for the AM shift for today. Then what would you like to happen (in detail)? I assume you randomly pick one of them and then do something based on their dept. On that note if you have a Dept field why do you need boolean fields for MTM, GIN..?

its real data but not for whole year

and the reason i put in the yes/no thought be easier to use in my where in sql query

when you have picked the people would like them to put into a weeks worth of rota so for 5 out of 7 in a table which will then populate a subform which will change based on date picker on top of main form
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:14
Joined
May 21, 2018
Messages
8,463
they would be assigned on skill base to specific area so thats the bit in the staff table which says MTM, GIN
All I can tell from your original design was that someone gets the AM shift and someone the PM shift for each day. There was no field for skills. Or do you assign a person to each dept for each shift?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:14
Joined
May 21, 2018
Messages
8,463
I normalized the data to demo this. Do you only have two shifts or can they be tailored? Are the following mistakes.

FirstName RotationDate RotationCode
Code:
KATARZYNA B	12/31/2018	07:00-14:00
KATARZYNA B	1/4/2019	          07:00-07:00
MARZENA S	1/5/2019	         08:00-15:00
VLADUT T	      1/28/2019	         07:00-07:00
JAROSLAW B	12/31/2018	10:00-10:00
JAROSLAW B	1/9/2019	        15:00-15:00
MARIUSZ K	12/31/2018	10:00-18:00L
MATEUSZ S	12/31/2018	10:00-18:00
JOLANTA P	12/31/2018	        10:00-18:00
ADELA B	12/31/2018	        07:00-13:00
ADELA B	1/7/2019	                07:00-07:00
EUGEN D	12/31/2018	       10:00-18:00

What should they be. If there is another shift 10-18 that is ok, but if they can be whatever then that would change the design I was planning.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:14
Joined
May 21, 2018
Messages
8,463
So I played with this and got a working demo for picking the random candidates. However, I may be wrong, but I think you may need to apply some rules and not just a random draw.
I assume you want to make the assignments fair. So you want to draw randomly but exclude those already drawn. Just because it is random does not mean there is not variance so if you did it purely random you may get someone with back to back assignments or a relatively big difference in number of assignments. To do that I run a query for each person for total number assignments. Then sort the query by number of assignments and then by the random order. That way you are always randomly pulling from the people with the least amount of assignments. In this way the difference at most is +/- 1 assignments.
If you want to make it purely random (like a drug test where just because you picked last time does mean you are out of the running) you just change the query to only use the random sort.
 
Last edited:

Users who are viewing this thread

Top Bottom