Calculate position in a series

[helmpost]

Folks,

I'm seeking a way to calculate positions (1st, 2nd 3rd etc) from a range of results in a series. The lower the calculcated number of points, the higher the position. Now this is fine except when the number of points gained is a tie.

In a sample series, the person with 3 points is in 1st place and he with 6 points is in 2nd place. However, there are two people with 8 points and two others with 11 points and a need to determine the relative positions. To solve this, there is a rule which states that in the event of a tie, the person with the best score in a single event is deemed to win the tie break - so if the tie is 8 points, the person with individual event scores of 1, 2 and 5 beats the person with an individual score of 1, 3 and 4.

I've been struggling with this, of and on for a month . I have access to all the data but despite all sorts of loops, cannot get the "right" answer. Has anyone come across this sort of thing before or can suggest a way forward?

Thanks in anticipation to anyone who may be able to help

Best Regards

Helm

 

[KenHigg]

Can you post the code for what you have working up to this point ?

Ken

 

[helmpost]

Thanks Ken, that was quick!

I hope you don't mind a page and a half of A4. This code taks me to a point where I have the results but there may be several people with the same number of points. You can probably see that the code includes mechanisms to select the best (lowest) results and that only those people who have the required number of events "qualify" for inclusion in the final results.


Private Sub Command18_Click()
On Error GoTo Err_Command18_Click

Select Case Me.Combo25
Case "All"
Me.Filter = "seriesID = " & Me.Combo12
Me.FilterOn = True
Case "Morning"
Me.Filter = "(tblResults.time = #11:15# or tblResults.time = #12:00#)& seriesID = " & Me.Combo12
Me.FilterOn = True
Case "Early Afternoon"
Me.Filter = "(tblResults.time = #13:30# or tblResults.time = #14:15#)& seriesID = " & Me.Combo12
Me.FilterOn = True
Case "Late Afternoon"
Me.Filter = "(tblResults.time = #15:15# or tblResults.time = #16:00#)& seriesID = " & Me.Combo12
Me.FilterOn = True
End Select

Dim rs As DAO.Recordset
Dim rsres As DAO.Recordset 'using tblseriesresults
Dim totsum As Byte
Dim nam, strmembername As String
Dim nrc As Byte 'number to count
Dim bo As Byte 'best of
Dim mybook As String 'dim a bookmark
Dim cnt1, cnt2 As Byte

ntc = Me![rtc] 'number of races to qualify for the series
bo = Me![bestof] 'best of
totsum = 0 'sum of points
cnt1 = 0 'counts the number of races to qualify
cnt2 = 0 'counts best of
nam = ""
strmembername = "The following people have " & ntc & " or more races to count" & vbCrLf & vbCrLf
'MsgBox strmembername
Set rs = Me.Recordset.Clone
Set rsres = CurrentDb.OpenRecordset("tblseriesresults", dbOpenDynaset)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With rs
.MoveFirst
.MoveLast
MsgBox .RecordCount


If .EOF And .BOF Then
MsgBox "There are no records that match your criteria"
Exit Sub
End If

.MoveFirst
Do While Not .EOF
nam = ![membername]
'create initial record in tblseriesresults and set qualify flag to "NQ"
With rsres
.AddNew
![membername] = rs![membername]
![qualified] = "NQ"
rsres.Update
' .Bookmark = .LastModified
End With
Do While ![membername] = nam
cnt1 = cnt1 + 1
cnt2 = cnt2 + 1
If cnt2 <= bo Then totsum = totsum + ![pos] 'while <= best of,count, else don't add further results
' MsgBox ![membername] & " cnt1 is " & cnt1 & " cnt2 is " & cnt2
If cnt1 = ntc Then 'when qualifying laps reached
' MsgBox ![membername] & " Qualifies with " & totsum ' give mesage and best results
strmembername = strmembername & ![membername] & " Qualifies with " & totsum & vbCrLf
With rsres
.Bookmark = .LastModified
.Edit
![qualified] = "Q"
![points] = totsum
.Update
End With
End If
.MoveNext
If .EOF Then Exit Do 'test for no further records
Loop
totsum = 0 'reset counters
cnt1 = 0
cnt2 = 0
Loop

MsgBox strmembername

End With

With rsres
.MoveFirst
Do Until .EOF
Debug.Print ![membername] & " " & ![qualified] & " " & !points
.MoveNext
Loop
End With

stDocName = "rptSeriesResults"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100

'delete records from tblSeriesResults in prep for next run
Dim wkspc As Workspace
Dim strdelete As String
strdelete = "DELETE * FROM tblSeriesResults"
Set wkspc = DBEngine.Workspaces(0)
Set db = CurrentDb
db.Execute (strdelete)
db.Close

Exit_Command18_Click:
Exit Sub

Err_Command18_Click:
MsgBox Err.Description
MsgBox Err.Number

End Sub

 

[KenHigg]

Hum...

Instead of me trying to cipher your code, maybe just post your table structure(s).

ken

 

[helmpost]

Ken,

Sorry. I think I can present the problem in a much simpler way. What it all comes down to is this. I have a number of people (sorry that it's text rather than numbers) that have taken part in a series. I then need to do this:-

1. test the number of events that each person has taken part in against a variable. Those that have less than this number are marked as "Not qualified"

2. test those left against another variable to get the best (lowest) results and add these together to get 1st, 2nd 3rd and so on

3. test for tied position, apply the rule and recheck positions

There's only really two table columns to look at - the person and the results from each event. Here's a sample results table. If variable (number of events) is 3 and the variable (best of) is 3 then there are two ties. For example, Chris Adlington ties with Lottie Rapson with 8 points and Chris wins because he has the best (lowest) individual scores (2,2,4) against (2,3,3)

membername points qualified
Basil Thompson 2 NQ
Basil Thompson 3 NQ
Chris Adlington 2 Q
Chris Adlington 2 Q
Chris Adlington 4 Q
Francis Baldwin 5 Q
Francis Baldwin 8 Q
Francis Baldwin 8 Q
George Shelley 2 Q
George Shelley 4 Q
George Shelley 5 Q
George Shelley 6 Q
Hester Allan 2 NQ
Hester Allan 3 NQ
Jim Shipp 3 NQ
John James 2 NQ
John James 3 Q
John James 4 Q
John James 4 Q
John James 5 Q
Lottie Rapson 2 Q
Lottie Rapson 3 Q
Lottie Rapson 3 Q
Lottie Rapson 4 Q
Lottie Rapson 5 Q
Lottie Rapson 6 Q
Martin Cushing 1 Q
Martin Cushing 1 Q
Martin Cushing 1 Q
Martin Cushing 1 Q
Martin Cushing 1 Q
Martin Cushing 1 Q
Mike Davy 2 NQ
Mike Davy 5 NQ
Rachel Thompson 1 NQ
Rachel Thompson 4 NQ
Rob Shelley 8 NQ
Robert Morris 3 NQ
Robert Morris 7 NQ
Roger Hilliard 7 NQ
Roger Hilliard 9 NQ
Tiffer Morris 2 Q
Tiffer Morris 3 Q
Tiffer Morris 6 Q
Tim Allan 1 NQ
Tim Allan 1 NQ
Tom Endacott 1 Q
Tom Endacott 2 Q
Tom Endacott 3 Q
Tom Endacott 4 Q
Tom Endacott 4 Q

Regards

 

[KenHigg]

Looks like doable logic... I'm juggling several things a one time here at work so it may be a while before I can look at it...

ken

 

[helmpost]

Ken,

By all means, whenever you have time

Regards, Brian

 

[geekay]

I dont think that your problem is a difficult one. But the way you present it makes it difficult to understand. If you want to get a solution you can attach a database with a few records, and a brief discription about what you really want. I can help you with codes in it

 

[helmpost]

Thanks Ken and geekay

I've attached a zipped file with a small amount of data and a query. When the sub is run, the user enters the minimum number of events required to qualify (x) and a "best of " value , so for example, the minimum number of events to qualify might be 4 (x) and we are looking for the best 3 results of those 4...

Aircoding what I am seeking to do :-

select a series of results (there's just one series in the attached tblresults table)
order by membername and pos asc (lowest value first)
from a variable (x) determine if each person has x records and if not set a
flag to ignore those persons in the overall results
from a variable determine the y (lowest) value records of those that are
left

reorder the records such that the lowest addition of y records is in first place and the next lowest in second place and so on

All this I can do ....

However, sometimes the calculations result in a tie, ie two or more people have the same calculated answer from y records. The tie break is decided by looking at the y records for each person - the person with the best (lowest) individual results wins the tie break. So if Chris had individual places of 2,2 and 4 he would beat Lottie who had individual places of 2,3 and 3.

I would need to continue testing until each tie was resolved and the series results had unique values for 1st, 2nd, 3rd place and so on. If this still results in a tie (and it could), increase the values of x and y by 1 and try again.

I hope this presents the issue more clearly?

Regards, Brian

 

[geekay]

Where is your attachmant?

 

[helmpost]

Sorry, first time I've tried an attachment. Should be there now?

Brian

 

[geekay]

Hi friend
I have done the coding for you. Testing is only remaining. Unfortunately I am away from home for few days for some duties. I can do any more works for you only after 26th July.
The database you supplied is not with sufficient data for testing. In the meantime you can supply a database with moderate number of series and each series consisiting of records having as many ties as you expect in an extraordinary situation.
Also let me know whther there will be a situation in which a tie will be unbroken even after applying tie breaking in all the possible levels as per the rules you suggested. I mean, will there be the same rank for two members in an extraordinary case? If so what is the strategy to be used? If there will be no such situation why should I waste time for handling such situation.
You can send the detailed database for testing in my email (rpgeorgekutty@gmail.com) also

 

[helmpost]

Thanks geekay

I've sent a larger database to your email address as requested.

with best regards

helmpost

 

[geekay]

I have returned your databse with the coding needed for the task
Best wishes

 

[helmpost]

Geekay,

I have just read through your solution. It works very well and will just slot into the rest of the application. It will take me some time to get my head around the approach - it looks very complex - but there look to be some great ideas that I can try to learn. You produced the code in ADO and suggested that I should consider using that rather than DAO. I have looked at ADO but I find DAO more logical and my reference books are all Access 97.

I'd like to say a very public thank you as it must have taken a great deal of your time. I will also apologise here for not providing laying out the complete structure at the outset. I don't know if you have a points system for experts on the forum but if it were in percentage effort terms then I'd like to offer 110%

with best regards

Helmpost

 

[geekay]

I hope you have discarded the first two mail attachments and using the 3rd one. Still I dont know from which country you are

 

[helmpost]

Hello Geekay,

Yes thank you, I have used the third version.

I am in Shrophire in the UK. The application is for my sailing club and seeks to replace the current system which relies on paper and some complex calculations undertaken by different people each week which inevitably results in error. This part of the application, which works out the results for each series of events, is undertaken by only one or two people who have the knowledge to apply some "human" logic to break the ties - but it has been a tedious process until now. To find out a bit more about us, please take a look at our web site at http://shropshiresailingclub.co.uk. The menu item "Racing" gives an insight into the series.

Best Regards

Helmpost

 

[geekay]

Check your mailbox to get the 4th version. It includes sub series ranking.
Thanks a lot for the experience given by your project .........Bye