Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2015
    Posts
    9

    Unanswered: swapping annual leave issue

    duplicated
    Last edited by gint32; 02-01-15 at 06:05.

  2. #2
    Join Date
    Jan 2015
    Posts
    9

    annual leave swap issue

    Same again
    Last edited by gint32; 02-01-15 at 06:06.

  3. #3
    Join Date
    Jan 2015
    Posts
    9

    help with annual leave swaps, with a slight twist to it.

    Hi All. I basically need help with the following staffing issue in connection with our Annual leave ROSTERS. we don't have a system at work presently to deal with annual leave swaps. At work all Staff are assigned annual Leave 6wks per year, these 6 week blocks are represented by alpha letters such as A,B, D, E, F, etc. As I said these letters correspond to 6 week blocks within each calendar year, this allocated leave letter remains with you throughout your employment. So for obvious reasons this doesnt suit every member of staff, so our HR Dept kindly allows us to swap with other willing staff members, so basically if we could find another staff member willing to swap letters then they will allow us to do the swap, on a one time only basis per year, meaning once swapped then you cannot later on then change your mind and request to swap the swap. How staff achieve this presently is to send out an e-mail to all staff requesting what you Have and what period you Want (it's a fairly large company with over 300 staff). This solution works reasonably well, but is obviously flawed (I'll explain why later on) as some staff will lose out. As you will only get a response(via Email) if someone has what you want e.g meaning Staff member1 has "A" and wants "B" period and staff member20 has "B" and really wants "A", because then its just a matter of both parties getting together and filling in a request form too give to the HR Dept(2Xway swap). What I would like to do is create a register so as our HR Dept can accommodate all our staff a little more better, then some staff will not lose out. Following is a very basic example of why staff do lose out on swaps that actually are available but get missed due to swaps only being available via e-mail (2Xway swap).

    E.g. take the following scenario:
    If a staff member1 sends an email saying that he has A and wants C, and another staff member2 reads this request who does have C but he wants B, then he's not going to answer the e-mail and so both staff members will lose out! as the person with C is not interested in taking A as he wants B which is a whole different six week block within the that year (it could be for a wedding or something so specific dates are important to get). But if we introduce a third staff member25 who say has B and wants A, then each of these 3Xstaff members would then be able to swap with each other as all staff involved would get what letter they wanted. (By the way I forgot to mention if staff member25 read any of the two e-mails he would not have answered either of them), I hope that makes sense
    A - C
    C - B
    B - A

    So the solution would be to create a register preferably in ms access 2010 (or Excel) and then somehow be able to run some sort of formula to find the matches/swaps even it were a 4,5 or 6Xway swap as they all would be happy.
    Heres what I have so far in VBA but it does not take into account if all parties involved are satisfied. As presently it just finds matches and swaps whether or not the swap has been satisfied at it's end, so I think it needs nested loops holding variables for each swap so it can undo if after swapping out if all parties involved in the swap are happy, but this is something way beyond my vba skills. Any help with this is appreciated. thanks

    Code:
     Sub SWAPpart1()
    Dim lRow As Long, x1 As Long, varTmp As Variant, tmpArr() As String
    
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    
    
    With Range("C2:C" & lRow)
    .Formula = Replace("=IFERROR(""A""&AGGREGATE(15,6,ROW($B$2:$B$@)/($A$2:$A$@=B2),COUNTIF(B$2:B2,B2)) & ""-A""&ROW(),"""")", "@", lRow)
    .Value = .Value
    End With
    End Sub
    
    
    
    Sub SWAPRun()
    Dim lRow As Long, x1 As Long, varTmp As Variant, tmpArr() As String
    
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    
    
    With Range("C2:C" & lRow)
    .Formula = Replace("=IFERROR(""A""&AGGREGATE(15,6,ROW($B$2:$B$@)/($A$2:$A$@=B2),COUNTIF(B$2:B2,B2)) & ""-A""&ROW(),"""")", "@", lRow)
    .Value = .Value
    End With
    
    With Range("D2 " & lRow)
    .Formula = Replace("=IFERROR(""A""&AGGREGATE(15,6,ROW($B$2:$B$@)/($A$2:$A$@=B2),COUNTIF(B$2:B2,B2)) & ""-A""&ROW(),"""")", "@", lRow)
    .Value = .Value
    End With
    
    
    For x1 = 2 To lRow
    If Range("C" & x1).Value = vbNullString Then
    ' NOTHING
    Else
    tmpArr = Split(Range("C" & x1).Value, "-")
    varTmp = Range(tmpArr(0)).Value
    Range(tmpArr(0)).Value = Range(tmpArr(1)).Value
    Range(tmpArr(1)).Value = varTmp
    End If
    Call SWAPpart1
    Next x1
    End Sub

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Personally id do this sort of thing manually. A simple A <-> C swap is easiy enough. Designing and testing an open ended swap that resolves all issues satisfactorily will be a pig. You could deploy a girm which recorded who wanted to swap and for what, but doibg the swap itself...... no thanks
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2015
    Posts
    9
    Quote Originally Posted by healdem View Post
    You could deploy a girm which recorded who wanted to swap and for what, but doibg the swap itself...... no thanks
    Sorry I don't understand what this means

  6. #6
    Join Date
    Jan 2015
    Posts
    9
    Quote Originally Posted by healdem View Post
    Personally id do this sort of thing manually. A simple A <-> C swap is easiy enough. Designing and testing an open ended swap that resolves all issues satisfactorily will be a pig. You could deploy a girm which recorded who wanted to swap and for what, but doibg the swap itself...... no thanks
    the following solution in vba is close"but no cigar " as it needs a little tweeking to work
    Code:
    Option Explicit
    
    Sub MakeSwaps()
    Dim vFIND As Range, vNEXT As Range, v As Range, LR As Long
    Dim i As Long, MyArr As Variant, Cnt As Long, buf As String
    
        Application.ScreenUpdating = False
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("D2:D" & LR).FormulaR1C1 = "=RC2&RC3"
        Range("E2:E" & LR).FormulaR1C1 = "=RC3&RC2"
        Range("F1") = "Swaps With"
        
        On Error Resume Next
        'this section handles all the direct swaps
        For Each v In Range("D2:D" & LR)
            If v.Value <> "" Then
                Set vFIND = Range("D:D").Find(v.Offset(, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
                If Not vFIND Is Nothing Then
                    v.Offset(, 2).Value = Range("A" & vFIND.Row).Value
                    vFIND.Offset(, 2).Value = Range("A" & v.Row).Value
                    v.Resize(, 2).Value = ""
                    vFIND.Resize(, 2).Value = ""
                    Set vFIND = Nothing
                End If
            End If
        Next v
    
        Range("E:E").ClearContents
        Range("D:D").SpecialCells(xlFormulas).FormulaR1C1 = "=RC2"
        
        'this section handles the multi-step swaps
        For Each v In Range("D2:D" & LR).SpecialCells(xlFormulas)
            If v.Value <> "" Then
                If WorksheetFunction.CountIf(Range("B:B"), v.Offset(, -1).Value) > 0 Then
                    Set vFIND = v
                    buf = v.Row
                    Do Until Cnt > 10
                        Cnt = Cnt + 1
                        Set vFIND = Range("D:D").Find(Range("C" & vFIND.Row).Value, LookIn:=xlValues, LookAt:=xlWhole)
                        buf = buf & "," & vFIND.Row
                        If Range("C" & vFIND.Row).Value = Range("B" & v.Row).Value Then Exit Do
                    Loop
                    If InStr(buf, ",") > 0 Then
                        MyArr = Split(buf, ",")
                        For i = UBound(MyArr) To 1 Step -1
                            Range("F" & MyArr(i - 1)).Value = Range("A" & MyArr(i)).Value
                            Range("D" & MyArr(i)).ClearContents
                        Next i
                        Range("F" & MyArr(UBound(MyArr))).Value = Range("A" & v.Row).Value
                        Range("D" & MyArr(UBound(MyArr))).ClearContents
                    End If
                End If
                Cnt = 0
            End If
        Next v
        Range("D:E").Delete xlShiftToLeft
        Application.ScreenUpdating = True
    End Sub

  7. #7
    Join Date
    Jan 2015
    Posts
    9
    I take it, that it's way to complicated then for this forum, based on the lack of response...thanks

  8. #8
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    gint32,

    "I take it, that it's way to complicated then for this forum, based on the lack of response...thanks"

    It's not too complicated, but this is an ACCESS forum.

    You can't expect a responder to present a solution when you haven't provided the
    schema for the database ... Is there an Access database? It's hard to present
    code without table/column names.

    Your Excel VBA code helps somewhat, but we don't have the data for the ranges!

    I think you might have understated the business rules. It's hard to write code
    for a specification, only to hear "That's almost it, but ...".

    Are there really only 3 leave blocks (A, B, C)?
    If so, how can they represent 6-week blocks?

    Must people take ALL of their vacation within one 6-week block? That's unusual.

    If this was a hospital, would you really allow all Doctors to take group A, all
    nurses to take group B and all Orderlies to take group C?
    You couldn't function that way.

    These are just some questions that occur just glancing at the information given.
    I'd concur with Healdem - do it manually.

    Given the information (and considering the data resides in Excel) do it manually
    until you move it into a database.

    Just my two cents worth,
    Wayne
    Last edited by WayCal; 02-10-15 at 02:26.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Frankly I saw the question as way too open ended, and one I wasn't prepared to get involved in detail. I think to find some form of automated process which can satisfy all dependencies fairly and be seen to be fairly (as you have to satisfy that as you are dealing with employees time off) is going to be fiendishly complex.

    I think you can go part way do the simple person 1 swaps with person 2. You coudl develop a system to register those requiring swaps, you could developing a system which you mnually allocate swaps, but I think to satisfy all the various dependencies will be a hurdle to far. You are not that far off the academics beloved 'travelling salesman' problem working out the various permutations, options and coutner options is going to be a pig when you start getting multiple people wanting to swap. A <-> B is easy, A -> C -> B -> A probably easily enough but with many people and many possible swap options.... not worht the time and effort I supect

    I guess you could do this using some pointers from linear programming but I suspect this is going to be far more an Access VB problem than an Access database problem.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2015
    Posts
    9
    Many thanks for your input, Re-ABC when you read my intial post properly, you'll read that ABC was only an example of why people would lose out. Yes there are more "alpha letters such as A,B, D, E, F, etc".they are A through to H, and yes we do have to take all 6 weeks at once unusual or not. Yes I would love to run it in MS Access but would not know where to start. Hence posing this question on the forum.
    Date ranges are as follows:
    Leave Letters Matching Date Ranges
    D 19/06/15 - 30/7/15
    C 31.07.15 - 10/9/15
    G 11.09.15 - 22/10/15
    H 23.10.15 - 03/12/15
    A 04.12.15 - 14/01/16
    B 15.01.16 - 25/02/16
    F 26.02.16 - 07/04/16
    E 29.04.16 - 19/06/16

    wishing to swap Swp_Range Preferred/Wishing
    A 04.12.15 - 14/01/16 C
    D 19/06/15 - 30/7/15 A
    B 15.01.16 - 25/02/16 D
    G 11.09.15 - 22/10/15 A
    G 11.09.15 - 22/10/15 E
    B 15.01.16 - 25/02/16 G
    F 26.02.16 - 07/04/16 B
    E 29.04.16 - 19/06/16 F

  11. #11
    Join Date
    Jan 2015
    Posts
    9

    All I have is the basic tables at present

    Quote Originally Posted by WayCal View Post
    gint32,

    "I take it, that it's way to complicated then for this forum, based on the lack of response...thanks"

    It's not too complicated, but this is an ACCESS forum.

    Wayne
    e at present which is just the basic tables and join

    This is all I have at present

    After hundreds of sorting columns manually I now think two way swaps should be the last type of the search to happen, as Ill show why. Take the following test data. Say we had matched the two first 2Xway swap using AC&CA. Now with the C&A pair used then the corresponding 21X way swap would not have been able to take place. Also, I have noticed that (after pairing the way I have done similar to below) then if you match any letter in Col A with any letter after that in Col B then all involved can safely swap as is the same the other way round ColB with anything matching in ColA form that point forward. Anyhows as you can see from the below sample most of the letters can swap in various places throughout, in essence there are many many possible swaps within this solution, but none of which would use all of these letters. So only the matches that contain the most number of swaps is in my opinion the best solution. I.e. the more people/leave-letters involved then the more people are satisfied and are happy.
    using the following:
    test data
    A < > C
    A < > B
    A< > D
    A< > D
    A< > G
    B< > E
    B< > A
    C< > D
    C< > A
    C< > E
    D< > E
    D< > C
    D< > B
    E< > C
    E< > H
    E< > A
    F< > G
    F< > A
    G< > H
    G< > F
    H< > F
    H< > C




    ...............................................
    Sorted swapable data...>
    A< >B
    B< > E
    E< > C
    C< > D
    D< > E
    E< > H
    H< > F
    F< > G
    G< > H
    H< > C
    C< > A
    A< > D
    D< > C
    C< > E
    E< > A
    A< > D
    D< > B
    B< > A
    A< > G
    G< > F
    F< > A

    The above 21X way swap would not be possible if I had initially went ahead and firstly swapped out the 2Xway swap AC<for>CA
    Attached Files Attached Files

  12. #12
    Join Date
    Jan 2015
    Posts
    9
    thought so

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Thought so (presumably in your mind its too complex)

    In my mind im notgoing to get involved. There is no identification of what the problem you want help with specifically. Yes there is an outline, yes there is a rudimentary specification.
    but a detail of i cant get this part to work. Not even heres my WIP im stuck on......

    What you expect may be reasonable in a paid for support contract, or asking for help within your organisation, but personally im not going to fritter away my time trying to ubderstand what problems may exist, and or develop a process for someone else.

    Now others may be willing to do that sort if thing, byr not me. Ill help as and where I can, as and when I can but yours is too open ended to pique my interest

    Its not a problem to do with complexity, Id argue its vavagueness and lack focus that is the issue. In a work capacity Id muck in, in my free time, no thanks
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •