Windows 7 64bit 
Access 2013
Hi Everyone,
I am revisiting this once again some 5yrs years later, I basically need help or advice with the following staffing issue in connection with allocation of A/L. Presently all Staff are assigned annual Leave letter representing a 6 wks slot per year, these 6 week blocks are represented by alpha letters from A,B,C,D,E,F,H. These letters correspond to 6 wk blocks within each calendar year, this allocated leave letter remains with you throughout your employment. For obvious reasons this doesn’t suit every member of staff, so our admin Dep’t allow staff to swap with another staff member willing to trade, so basically if we could find another staff member willing to swap the leave letter then Admin will allow us to do the swap, on a one time only basis per year, meaning once you’ve swapped then you cannot later on then change your mind and request to swap the swap. So how staff reach out to achieve this presently is to send out an e-mail to all staff requesting what letter you have and what letter (period) you want, this is a fairly large company with over 400 staff.
This system doesn’t works really well, because if a person sends out an Email request to swap, they will only get a response(via Email) if someone has what you want, which is usually not the case, also if something crops up its really difficult to keep track of all the E-mailed requests that were sent throughout the year, even if you do archive these they may have actually found a swap and therefore are now no longer interested in swapping.
Take the following example:
John Doe has "A" but needs the leave period represented by letter "B"
Mary Smith has "B" but needs the leave period represented by letter "A"
This then is easy and it’s just a matter of both parties getting together and filling out the appropriate paperwork for the admin to process AKA (2Xway swap).
But this way is obviously flawed as most staff will lose out and will not get the correct time of year that they need to be off work (ie weddings, emergencies, family (in other countries), match partners leave, etc! .. this is a real pain for most staff
Take the following example:
John Doe has "A", but needs the leave period represented by letter "B" (A-B)
Mary Smith has "B", but she wish leave period represented by letter "C" (B-C)
Tom Thumb has "F", and he needs letter "A" (F-A)
Adam Ant has "C", but he needs leave period "G" (C-G)
Jiminy Cricket "D", but he needs leave period "G" (D-G)
Frank Jones "G", and he needs letter "A" (G-A)
With the above example Tom & Jiminy would remain on the register and wait for more staff to go on the swap register. But as you can see from the below…4 Staff members will “NOW” get the leave period they require. Who otherwise would have had to miss out on an important event.
John A-B John A – B
Mary B-C Mary B – C
Tom F-A Frank G – A
Adam C-G Adam C –G
Jiminy D-G
Frank G-A
OR <---------------------------------------------------------------------------------------- OR
John A-B John A – B
Mary C-A Mary C – A
Tom B-C TOM B – C
Adam C-G
Jiminy D-G
Frank G-A
Not sure if it’ll help, but when I swapped the letters manually I’ve spotted a consistent pattern in the above examples,
e.g. As long as a value within Column “A” contains the same value that’s also in Col B along with the count of each being the same then all letters within can swap, and if not they’ll remain on the register till a match values are in each column
SO, what I need to do is create a register and then somehow be able to run some sort of formula to find the matches/swaps even it were a 4, 5 or 6.. way swap. so as our Admin staff can accommodate all our staff a lot more efficiently, that way a good number of staff will not lose out as they do every year.
The formula is where I need the help as I don’t have much experience in pattern matching, as creating the register to hold all the staff names and letters I assume would be the easy part!
I imaging I’d need to start by sorting on values (from A-Z ) in column A, then loop through cross ref and matching values with Column B then move these letters out into another vacant column or table if a match is found!
Any help or advice on a viable solution would be much appreciated.
Reply With Quote
Originally Posted by Micron
