annual leave issue

[gint32]

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 doesn’t suit every member of staff, so our HR Dep’t 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 Dep’t 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 previous 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 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.
Here’s 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

[gint32]

see attachment as it would not let me post the code for some reason

[gint32]

I take it from the lack of response that it to complicated for this forum. Thanks

[Bob Phillips]

I think that the fact that you posted an Excel problem in an Access forum will have more to do with the lack of responses. I personally never even read it first time around.

The problem seems clear enough, but you need to post the workbook, that will help enormously. If you can post a Word doc, you can post an Excel workbook.

[gint32]

Apologies, as Excel content is really all I have at this point, I really need the solution done with Access 2010/13 though.

I really think the best way to solve this is to firstly find and mark/remove all the direct 2X way swaps such as.



Staff 1 has “A” wants “B”
Staff 5 has “B” Wants “A” then remove all 2 way matches


Then search for all combinations of matches in the following way (not that I know how to do this in VBA): take this scenario

A-D
B-A
C-F
E-G
C-H
F-A
D-B
G-C

Presently, It would be difficult to know “who can swap with who” unless you run through each value line by line and see check for this which is very complicated.
But, I think there is a much easier way to find out really quickly.
Which is to use code to search for matches, then from the above sample search for matching values that are in each column that are on the "same row", meaning Column A = (A,B,D) & on the same rows we have (D,B.A) and if both columns are true, then at the point we know we can safely swap knowing that each person is happy without actually figuring out who actually swaps with who .

[jonh]

I'd probably try to solve it like a recursion test.

Follow the path until you hit an element that has already been processed.
If the first and last items match you should (?) have a matching set.

e.g.

A-D
B-A
C-F
E-G
C-H
F-A
D-B
G-C

A wants D
D wants B
B wants A

A1
D1
B1
A2 <-- recursion / match

F1
A1
D1
B1
A2 <-- recursion / no match


That would be easy enough for unique values in the left column, but I think the duplicates would complicate things quite a lot...

E1
G1
C[#1]1
F1
A1
D1
B1
A2 <-- recursion / no match
C[#2]1
H1 <-- end of data / no match

[gint32]

I'd probably try to solve it like a recursion test.

Follow the path until you hit an element that has already been processed.
If the first and last items match you should (?) have a matching set.

e.g.

A-D
B-A
C-F
E-G
C-H
F-A
D-B
G-C

A wants D
D wants B
B wants A

A1
D1
B1
A2 <-- recursion / match

F1
A1
D1
B1
A2 <-- recursion / no match


That would be easy enough for unique values in the left column, but I think the duplicates would complicate things quite a lot...

E1
G1
C[#1]1
F1
A1
D1
B1
A2 <-- recursion / no match
C[#2]1
H1 <-- end of data / no match

Sorry, you've lost me on that explanation:dunno

[gint32]

I'd probably try to solve it like a recursion test.

Follow the path until you hit an element that has already been processed.
If the first and last items match you should (?) have a matching set.

e.g.

A-D
B-A
C-F
E-G
C-H
F-A
D-B
G-C

A wants D
D wants B
B wants A

A1
D1
B1
A2 <-- recursion / match

F1
A1
D1
B1
A2 <-- recursion / no match


That would be easy enough for unique values in the left column, but I think the duplicates would complicate things quite a lot...

E1
G1
C[#1]1
F1
A1
D1
B1
A2 <-- recursion / no match
C[#2]1
H1 <-- end of data / no match

As I said recursion is way out of my grasp, could you possibly show a peice of code that would find distinct values in the two columns ..eg search for values in one column thats not in the other, at least that'll be a start for me.

After sorting hundreds of test data manually, I now think two way swaps should be the last type of the search to happen, as I’ll show why. Take the following test data. Say we had matched the two first 2Xway swap using AC&CA. With the CA pair now used, then the corresponding 21X way swap could not have been able to take place. Also, Yes, I agree 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. Anyway, as you can see from the below sample data, 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. So to start with I need to get the code to firstly remove all entries that are in one column that are not in the other, then to do the same the other way round.












A < >

C

……..


A

<>B



A < >

B

……..


B< >

E



A< >

D

……..


E< >

C



A< >

D

……..


C< >

D



A< >

G

……..


D< >

E



B< >

E

……..


E< >

H



B< >

A

……..


H< >

F



C< >

D

……..


F< >

G



C< >

A

……..


G< >

H



C< >

E

……..


H< >

C



D< >

E

……..


C< >

A



D< >

C

……..


A< >

D



D< >

B

……..


D< >

C



E< >

C

……..


C< >

E



E< >

H

……..


E< >

A



E< >

A

……..


A< >

D



F< >

G

……..


D< >

B



F< >

A

……..


B< >

A



G< >

H

……..


A< >

G



G< >

F

……..


G< >

F



H< >

F

……..


F< >

A



H< >

C

……..

[jonh]

To get a list of direct matches you can use a query

(I created a table called AL with fields PersonID, ALHas and ALWant)


SELECT AL.PersonID, AL_1.PersonID
FROM AL INNER JOIN AL AS AL_1 ON AL.ALHas = AL_1.ALWant

To find values that aren't in the other column you need a query for each, e.g.

Nobody has


SELECT AL.PersonID
FROM AL LEFT JOIN AL AS AL_1 ON AL.ALWant = AL_1.ALHas
WHERE AL_1.PersonID) Is Null

Nobody Wants


SELECT AL_1.PersonID
FROM AL RIGHT JOIN AL AS AL_1 ON AL.ALWant = AL_1.ALHas
WHERE AL.PersonID) Is Null


I made a quick example of the type of check I was trying to explain @ post #6

I don't understand your data above so I'm still using the data you posted @ #5, i.e.

A-D
B-A
C-F
E-G
C-H
F-A
D-B
G-C

The code gives this output (again using the AL table described above.)

So it finds 3 sets of matches. I don't know if that's right, I havn't bothered to check.


1AD - 7DB
7DB - 2BA

3CF - 6FA

4EG - 8GC
8GC - 3CF



Dim aPID() As String 'Person ID number
Dim aHas() As String 'A/L letter
Dim aWant() As String 'A/L required
Dim aCount() As Boolean '0 if not matched

Private Sub Command0_Click()
'fill arrays from table
FillArr

Dim i As Long
For i = 0 To UBound(aPID) - 1
'do search (if not already matched)
If Not aCount(i) Then match i
Debug.Print
Next
End Sub

Private Sub match(pid As Long)
Dim f As Long
f = afind(aWant(pid))
If f Then
Debug.Print aPID(pid) & aHas(pid) & aWant(pid), aPID(f) & aHas(f) & aWant(f)
match f
End If
End Sub

Private Function afind(find As String) As Long
'find unmatched record
For i = 0 To UBound(aHas) - 1
If aHas(i) = find Then
If Not aCount(i) Then
'mark record as matched so it isn't matched again
aCount(i) = True
'return record number
afind = i
Exit Function
End If
End If
Next
End Function

Private Sub FillArr()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("select personid,alhas,alwant from al")

ReDim aPID(0)
ReDim aHas(0)
ReDim aWant(0)
ReDim aCount(0)

Do Until rs.EOF
i = UBound(aPID)
aPID(i) = rs(0)
aHas(i) = rs(1)
aWant(i) = rs(2)
rs.MoveNext
i = i + 1
ReDim Preserve aPID(i)
ReDim Preserve aHas(i)
ReDim Preserve aWant(i)
Loop
ReDim aCount(i)
End Sub

[gint32]

To get a list of direct matches you can use a query

(I created a table called AL with fields PersonID, ALHas and ALWant)


SELECT AL.PersonID, AL_1.PersonID
FROM AL INNER JOIN AL AS AL_1 ON AL.ALHas = AL_1.ALWant

To find values that aren't in the other column you need a query for each, e.g.

Nobody has



Thanks for that, I am not sure where or how you are running the subs you supplied might seem obvious to you, but I am a novice so could you please explain that part a little bit easier for me so as I can try and replicate what you are talking about. many thanks in advance ...see the attached (as thats as far as I could get).

[jonh]

Create a new form and add a button.
Go to the form's module and paste the code.

The output is written to the Immediate Window. Run the code and go back into the module and select View > Immediate Window or press CTRL + G

From your sample, I get the output


AliceAC MarybCD
MarybCD thomasDE
thomasDE CharlesEC
CharlesEC JohnieCA

Alice2A B SammyBE
SammyBE BernardEH
BernardEH WilliamHF
WilliamHF MargaretFG
MargaretFG AllanGH
AllanGH FrankieHC
FrankieHC FionaCE
FionaCE LilianEA
LilianEA MaryAD
MaryAD JamesDC


TomAD JohnDB
JohnDB JimmyBA
JimmyBA TomAD

GerryAG ReaceGF
ReaceGF TroyFA
TroyFA GerryAG

[jonh]

Or if you are talking about the queries

create > query design > close 'show table'

click SQL in the top left, paste the code

[gint32]

Create a new form and add a button.
Go to the form's module and paste the code.

The output is written to the Immediate Window. Run the code and go back into the module and select View > Immediate Window or press CTRL + G


Thanks, But I get nothing(no output) as I am not sure how it all works, I can see the code in the imediate window, Also, I can step though the code in the imediate window, but where's is the output went to or headed to? Is it to a temp place or a make table the disappears after running or what? As I get no output to anywhere visible to me, sorry but I am missing something very basic I know. So could someone tell me how to get output in another way or explain it as if you were talking to a 5year old. many thanks. see the attached for my terrible effort

[jonh]

Code goes in the module.
Debug.print writes to the Immediate Window.

[gint32]

Code goes in the module.
Debug.print writes to the Immediate Window.
I finally found where the immediate window is located. So for those novices like me who read this post and who are also complete idiots like me when it comes to programming and you also have come across the same issue, with pressing Control + G and getting nothing, then for your own info, you will never get the immediate window to come up when pressing Control + G as the last post by “Jonh” wrongly suggests.
The windows is or was there all along, but it was hiding at the bottom part of the screen and the only thing I can liken it to is a split screen set up, but I had to search on google to find out where it was, so basically the short story is everything is easy when you know how and in my case if you don't know then you really don't know “Statement of the obvious“. Heres how you can expose the immediate window if it’s not showing like it was in my case, you start by placing the cursor on or near the bottom of the screen when view your code windows(Alt+F11) and then by dragging a line that goes all the way across the bottom of the screen in an upwards direction to reveal. It would have a lot less painfull to me if someone had just explained this. So hopefullly I'll have saved someone else from going through what I have just been through.:dunno

[jonh]

Aw shucks.

Please mark thread solved. Thanks.

[gint32]

Aw shucks.

Please mark thread solved. Thanks.


There is no need to be rude or sarcastic just because a person doesn't know the answer to a question or suggestion he poses, please be more respectful to me or anyone who poses legitimate questions. FYI, I am an educated man but obviously not in this field and in any case isn’t this part of what having online forums are for?

[jonh]

I'm not the only one in this thread displaying a lack of tact.

Respect goes both ways.