syntax for annual leave roster

[gint32]

Hi All,
I basically need help with the following staffing issue in connection with our Annual leave “ROSTERS”. What we have at our work presently is, …. Here goes; Presently staff are allocated a Leave Letter such as A,B, D, E, F, G etc. These letters correspond to 6 week blocks within each calendar year, the allocated letter will remain with you throughout your employment although it does roll over to the following block each year. So for obvious reasons this doesn’t suit every member of staff, so our HR dep’t came up with the idea that if we could find another staff member willing to swap you in a three or six week blocks (in other words only 1^st 3wks or last 3 wks of the block, or all 6wks) then they would be happy to accommodate both parties. This solution work reasonably well, but is obviously flawed as some staff will lose out.
E.g. take the following scenario:
If “A” wants “C”, and “B” wants “A”, “C” wants “B”.
“A” who wants “C” loses out , as the person with “C” is not interested in taking “A” as he wants “B”.
But if all three got together, they could all swap at the same time ( 3-way swap) and then everyone is happy, now the thing is we need a register or DB’s to cope with the calculation. As if that’s not enough of a problem, to make matters complicated even further (syntactically), the bigger the amount of staff involved in a 3, 4, 5, 6 way swap; then the greater amount of people that will be accommodated and in turn will be happy with it.
So let’s say we have a db with a table for the names, letters and date ranges sought after and another table with all leave letters names and date ranges offered.
Then a syntax solution possibly using Dcount to look for duplicates matches based on date range value’s >= & =< and also would be looking for max value of exact matches (based on date ranges, in the haves column to the wanted column, then arrange the returned values with staff names and then send to a make table and or print out results to some sort report of who swaps with who. The following register would make 5 X members of staff happy if they get together ( if you can spot the pattern)

	Have Letter	Date range	Sought letter	Date Range	Swap X5
Staff 1	A	<Some dates>	B	<Some other>	Staff 1 <> 2
Staff 2	B	“”	C	“”	Staff 2 <> 3
Staff 3	C	“”	D	“”	Staff 3 <> 4
Staff 4	D	“”	E	“”	Staff 4 <>5
Staff 5	E	“”	F	“”	Staff 5 <> 6
Staff 6	F	“”	A	“”	Staff 6 <> 1
Staff 7	G	“”	G	“”	Staff 7 > ???

Any help or thoughts appreciated

[rpeare]

Are you still working on this issue?

[gint32]

Are you still working on this issue?

Yes thanks, and still waiting on response from anyone..

I have read through quite a few similar issues(VBA code wise), I think (partly) the solution that I am looking for is just to find the best way to find values of what doesn't match between two columns(Value wise), and then from whatevers left from that search Staff membes would be able to swap with each other the following simple example shows that staff members 1 to 6 can do this swap:

'-----------------'	Has-leave Letter	Wanted leave letter	Swap = WayX6)
Staff member1	A	B	Staff 1 swaps with 2
Staff member2	B	C	Staff 2 swaps with 3
Staff member3	C	D	Staff 3 swaps with 4
Staff member4	D	E	Staff 4 swaps with 5
Staff member5	E	F	Staff 5 swaps with 6
Staff member6	F	A	Staff 6 swaps with 1
Staff member7	G	H	Staff 7 > can't help as no match as yet???, So goes on waiting list
Staff member8	G	A	Staff 8 > can't help as yet(as Staff member1 asked first) cronologically speaking

Might be able to be something as simple as using what ever is the opposite of DistinctRow:...
Some input would be helpful ;

below is the corresponding leave letter to date blocks for 2015 -2017 to hopefully make it clearer:

2015-2016	2016-2017
D	G
19.06.15	10.06.16
30.07.15	21.07.16
C	H
31.07.15	22.07.16
10.09.15	01.09.16
G	D
11.09.15	02.09.16
22.10.15	13.10.16
H	C
23.10.15	14.10.16
03.12.15	24.11.16
A	F
04.12.15	25.11.16
14.01.16	05.01.17
B	E
15.01.16	06.01.17
25.02.16	16.02.17
F	A
26.02.16	17.02.17
07.04.16	30.03.17
NL*3 Weeks	NL*6 Weeks
08.04.16	31.03.17
28.04.16	11.05.17
E	B
29.04.16	12.05.17
09.06.16	22.06.17

[rpeare]

I would like to ask a few questions because this problem intrigues me.

1. what does the NL*3 Weeks and NL*6 Weeks mean in your vacation date ranges?
2. Are people only allowed to swap date ranges within the same fiscal year?
3. Once a person is allowed a swap, can that ever change, for instance, let's say person A wants to swap vacation periods. Let's assume for the sake of argument you find a matching person and inform that person they can swap to their desired vacation period. Now, a month later, 3 more people put in for vacation period swaps, do you ever re-evaluate people who have requested leave changes in the past? Or is your rule, once you have had your vacation period swap confirmed it can't be changed unless you request another change?

[gint32]

1. what does the NL*3 Weeks and NL*6 Weeks mean in your vacation date ranges?

• Stands for No leave during these periods ( by the way, all staff are prison officers)

2. Are people only allowed to swap date ranges within the same fiscal year?

• Yes, only allowed to swap date ranges within the same fiscal year

3. Once a person is allowed a swap, can that ever change.

• No, Can’t swap a swap that’s it. (Hence the need to have a MDB) as presently you we only have the facility for a 2Xway swap ), This is process at the moment for a swap is one person send out a general e-mail to all staff and ask if any one want to swap; A for H or another example would be 1^st 3wks of C for the last 3 of H..etc, etc

People can and do put in for vacation period swaps all the way throughout the years(E-mail) , but once swapped that’s it. No re-evaluation for people who have had changes for . So nce you have had your vacation period swap confirmed it can't be changed.

Hope this makes more sense…see attached spreadsheet that gets sent out to staff from HR. As I said previously, staff using this method are losing out due to only being able to ask for a 2X way swap, Presently, only staff with G can answer this request..which is crazy as the more involved in the swap the more people get a swap out of over 250 officers in each prison this has to happen asap, But I have to get a MDB/register that we can input our names on and up and running with no glitches, before I even approach HR or they’ll likely say get stuffed as it’s to much work for them.


Below is an actual(real) E-mail request (name removed of course):

Subject: Annual leave swap opportunity

Hi there

Anyone interested in swapping either:
2^nd 3ks of Leave Letter “G” 02/01/2015-22/01/2015 or,
1^st 3ks of Leave Letter “H” 23/01/2015-12/02/2015
In return you can select your choice from Leave Letter “F”. Will also consider swapping all of Leave Letter “G” for my Leave Letter “F”.

Cheers John ??????leave letters2015114.zip

[rpeare]

So let's make sure I understand the rules

Vacation periods are 6 weeks long

Staff can request a full 6 week swap
Staff can request a 3 week swap for either the first 3 weeks or the last 3 weeks of a period

Staff can NOT request any other sub breakdowns (4 weeks or 5 weeks of the 6 week period)

Staff can swap their original period 1 time and only 1 time per 3 week block

example Staff A can swap the first 3 weeks of his original period and the last 3 weeks of his original period independently of one another but once the swap has occurred he is stuck with his/her lot.

Requests will be honored in the order they are received as much as possible (this is different than finding the best possible match and is easier to accommodate I think)

Is all of this correct and are there any other rules?

[gint32]

Absolutely correct, not only that but he may be have the 1st 3weeks and wishing the 2nd 3weeks of a block eg.( (A1 for D2) or A2 for D2) as a last resort, meaning he is desperately needs to be off on the D2 3wk time period, but if given the choice he would rather just swap A1 for D2, as he may need A2 for some other urgent time period within the fiscal year such as a wedding etc. But then again the only option that might be available on the register (which at present we do not have) is to swap a straight A for D if he really had no other option. So there’s is lots of possible combinations...the best way to visualize it is to put the shoes on of that person and then come up with the idea that although that period was ok , at the start of your employment. Now isn't due to an unforeseen upcoming event, He will not get going unless he gets a swap for those dates... and the only option at present is a 2 way swap as we don't have a register as yet. hopethat helps explain.

Below is an actual(real) E-mail request (name removed of course):

Subject: Annual leave swap opportunity

Hi there

Anyone interested in swapping either:
2nd 3ks of Leave Letter “G” 02/01/2015-22/01/2015 or,
1st 3ks of Leave Letter “H” 23/01/2015-12/02/2015
In return you can select your choice from Leave Letter “F”. Will also consider swapping all of Leave Letter “G” for my Leave Letter “F”.

Cheers everyone!
John
This post has been edited by gint32: Today, 04:45 AM

[gint32]

So let's make sure I understand the rules

Vacation periods are 6 weeks long

Staff can request a full 6 week swap
Staff can request a 3 week swap for either the first 3 weeks or the last 3 weeks of a period

Staff can NOT request any other sub breakdowns (4 weeks or 5 weeks of the 6 week period)

Staff can swap their original period 1 time and only 1 time per 3 week block

example Staff A can swap the first 3 weeks of his original period and the last 3 weeks of his original period independently of one another but once the swap has occurred he is stuck with his/her lot.

Requests will be honored in the order they are received as much as possible (this is different than finding the best possible match and is easier to accommodate I think)

Is all of this correct and are there any other rules?

does the info supplied make sense, or do you need more info?

[rpeare]

I think so, but I am a bit busy for the remainder of the week and I'll see what I can do this weekend.

[rpeare]

This is turning out to be way more complex than I imagined

For instance if this is my dataset (assuming the first record is the first entered)

Let's assume:
1. Employee A wants to swap his/her entire vacation block (A) for vacation block B

This is actually four possible requests:
(1st Half A for 1st Half B *OR* 1st Half A for 2nd Half B) *AND* (2nd Half A for 1st Half B *OR* 2nd Half A for 2nd Half B)
Meaning this person could actually swap different halves with different people to get what they want

2. Employee B wants to swap the first half of his/her vacation block (B) for any part of vacation block A

This is actually 2 possible requests:
1st Half B for 1st Half A *OR* 1st Half B for 2nd Half A

3. Employee C wants to swap the the 2nd half of his/her vacation block (B) for the 1st half of vacation block A

This is a single request
2nd Half B for 1st Half A

Let's assume the requests are entered in the order listed Only one match would be found for Employee A and one match for Employee B

Employee A: 1st Half A to Employee B: 1st Half B
and
Employee B: 1st Half B to Employee A: 1st Half A

When the best possible match would be
Employee A: 1st Half A to Employee C: 2nd Half B
Employee A: 2nd Half A to Employee B: 1st Half B
Employee B: 1st Half B to Employee A: 2nd Half A
Employee C: 2nd Half B to Employee A: 1st Half A

So I think you do not want to process these strictly in the order they are requested and logically the only way I can think of to get the best possible match is to try every possible combination of request block vs requested block and count the matches. After determining which order yields the most matches then update your tables.

If you've got 250 employees and the worst case scenario is that every employee wants to swap their full block of vacation days for a different block that's (250*4)^2 possible permutations or 1,000,000 records to process.
If your vacation swaps are periodic and fairly small it may be easier on the processing side.

So what do you actually want to do:
A. Match based on when the request comes in ONLY - this will not yield the best match
B. Match based on counting successful matches from every combination of requested block changes - this will yield the most 'happy people'

[rpeare]

P.S. I was wrong about counting possible combinations it's computed by factorials thus with 7 swap records (from the example above) you would have to cycle through 7 groups of 6! (720 records) or 5040 records in total. Granted you would end up skipping a bunch of them simply because they had been eliminated from consideration (particularly towards the end of each group) but you can see where this would blow up quickly. How many swap requests do you think you'd face (as a maximum) based on the methodology I've listed above.

[gint32]

Letter	Discription
A	All 6weeks
A1	1st three weeks of the leave block(date range)
A2	2nd Three weeks of the leave block
B	All 6weeks
B1	1st three weeks of the leave block(date range)
B2	2nd Three weeks of the leave block
C	All 6weeks
C1	1st three weeks of the leave block(date range)
C2	2nd Three weeks of the leave block
D	All 6weeks
D1	1st three weeks of the leave block(date range)
D2	2nd Three weeks of the leave block

[gint32]

Would have to be “B” to yield the most 'happiest amount of people'
Not sure this would blow up quickly. As it's more of a pattern of matches that the code should be looking for.

I think the best way to crack this "put simply is": Firstly, search for all matches in one column –“wanted” that match in the other column- “have” ( one for one only basis though) by possibly using an array or query combination. So to use your own example: as you can see from the below, the criteria to search for would be to find 2xA’s, 1xB and 1xC

A= all
A1= 1st block
A2 = 2nd block
and so on.


When the best possible match would be:

Employee A: (1st Half) A1 (to Employee) C2: (2nd Half B)
Employee A: (2nd Half) A2 (to Employee B1: (1st Half B)
Employee B: (1st Half) B1 (to Employee) A2: (2nd Half A)
Employee C: (2nd Half) C2 (to Employee) A1: (1st Half A)


After which something like ;
intCount = DLookup("qry_matches.AppsCount", "qry_matches")

If intCount = 0 Then
Exit Sub
Else
If MsgBox("There are " & intCount & " matches, " & _
vbCrLf & vbCrLf & "In the annual leave register, Would you like to see these now?", _
vbYesNo, "...") = vbYes Then
DoCmd.Minimize
DoCmd.OpenForm "frm_Matches", acNormal
Else
Exit Sub
End If
End If
End Sub

2nd, 3rd 4th etc choices should be held on the same record but in different columns(e.g fields), so as if not found in prefered column then compare 2nd, then 3rd etc

[gint32]

P.S. I was wrong about counting possible combinations it's computed by factorials thus with 7 swap records (from the example above) you would have to cycle through 7 groups of 6! (720 records) or 5040 records in total. Granted you would end up skipping a bunch of them simply because they had been eliminated from consideration (particularly towards the end of each group) but you can see where this would blow up quickly. How many swap requests do you think you'd face (as a maximum) based on the methodology I've listed above.

The amount of swap records, I usually see (via E-mail requests) similiar to the post sample I supplied earlier are any where from 1-20 per month, am I making sense so far or have I stumped everyone? although most requests are for straight swaps.

[rpeare]

Google and IBM have invested millions if not billions of dollars in detecting patterns, not to mention the computing power to process them, I'm just a lowly access programmer.

Based on your definition each vacation block can be broken into 2, 3 week blocks. So if someone wants to swap his/her entire vacation block for different periods there are 4 possibilities (2 original vacation periods of 3 weeks *TIMES* 2 requested vacation periods of 3 weeks), there is no full block or maybe 2 3 week blocks, you have to program to handle any combination of vacation substitution requests and that is based on your smallest unit (3 weeks).

If you have 20 requests in a given month that represents 2,432,902,008,176,640,000 possible combinations if you are looking for the best possible matches. There is no way access can handle that in any reasonable amount of time.

Look at a simple example

Staff A has vacation period A
Staff B has vacation period B

Staff A wants to swap his entire block for period B
Staff B wants to swap his entire block for period A

This is *not*, in terms of programming, a 1 for 1 examination

This is

Staff A - Period A, 1st Half TO Period B, 1st Half
Staff A - Period A, 1st Half TO Period B, 2nd Half
Staff A - Period A, 2nd Half to Period B, 1st Half
Staff A - Period A, 2nd Half to Period B, 2nd Half

and of course you have the exact set of swap requests on the Staff B side.

Essentially in programming you have to find a way to try every possible combination of these requests to see which is the most, the computer will never infer a match, you have to program something that is based on logic of the data presented. For the purposes of this example you will end up with 4 different combinations that all have the same number of satisfied links but you will have 8 different 3 week swap possibilities which would yield 8! combinations or 40320 possibilities.

1. a1 - b1
2. a1 - b2
3. a2 - b1
4. a2 - b2
5. b1 - a1
6. b1 - a2
7. b2 - a1
8. b2 - a2

Remember the computer is dumb it can only follow the logic designed by the programmer, it can not make leaps of logic, to find the best match it has to process every possibility to know for sure. I'm not a statistician and there may be some mathematical ways to eliminate possibilities under certain circumstances but I don't know them. I hate to give up on a problem but a 'best match' program, while not impossible, would be incredibly time consuming in access alone, to the point of being useless for anything beyond a small number of requested swaps.