Sorting - Bubble Sort?

[helmpost]

Folks,

in my Access app I'm looking for an approach to dealing with ties in series of races. The winner of each race gets 1 point, second place gets two points etc. Races are parts of series and it is the series results that give me the problem. For example

(see seriesresults.doc)

You can see that 4th place and 8th place are tied. To resolve the ties, all the race results for those that have tied must be inspected to see who has the best collection of lowest values as in this test set ...

(see tievalues.doc)


By inspection, the ties cannot be resolved in the first column since all the tieing parties have the same values. In the second column, either Tom or Brian will win and finally it can be resolved in column 3 so the tie break is Tom is in 4th place, Brian in 5th place and Ben in 6th place.

An additional complication is that if the tie break has to keep going across many columns (races) , Tom, Brian or Ben may have less overall results and the tie break then is that any value is better than no value at all. Ultimately, it's possible that the ties cannot be resolved.

My research suggests that the solution probably needs an array and a bubble sort - neither of which I have used before.

Can anyone suggests how this could be coded? I do appreciate that the solutions needs to be run for each set of tied places i.e. 4th, 8th

Thank You.

 

[MarkK]

If the data originate in tables you can join the tables and assert a sort order in a query using an ORDER BY clause. In a worst case scenario, create a temporary table with the fields you need, push the data into a table, and sort it that way. It seems unnecessary to write your own sorting routine when you have a database engine sitting around looking for data to crunch.

 

[helmpost]

Thanks lagbolt that sounds encouraging ...

The new table would look something like the attached?

(see tievaluetables)

I still don't see though how this would help me to sort the data. I think there's a need for some sort of bubble (or other sort) across the columns that stops when there is a clear solution - or it reaches the last column without a solution. In this case, the tie break is resolved at column field4 but with other data could be resolved earlier or later depending on the data values.

Sorry if I have missed the point

Regards

Helmpost

 

[The_Doc_Man]

I think there's a need for some sort of bubble (or other sort) across the columns that stops when there is a clear solution

No.

Sort using ORDER BY with a list, such as

SELECT ...... FROM ..... WHERE .... ORDER BY Primary-Criterion, Tie-break-1, Tie-break-2, Tie-break-3, Tie-Break-4, .... ;

This will order the records in the appropriate order. If that is a query that supports it, you can then use it in an UPDATE query to update rank ordering by storing the count of records with better result, +1. Or drive VBA code through the resultant recordset.

Me, I'd do the VBA thing because it is easier for me. And I could then detect that rare case where the primary and all tie-breaker criteria are tied, showing that I have a true tie that cannot be resolved.

 

[helmpost]

The_Doc_Man and lagbolt.

Bingo! I guess I have never paid attention to how ORDER BY is working.

It does the job even where is no value in one of the fields. But as The Doc Man points out, there's no way to tell if there is a genuine tie at the end of the process. Can I impose on more of your time and ask The_Doc_Man how he would go about this in VBA such that a genuine tie would be recognised?

Regards

Helmpost
(also a certified Grandad)

 

[MarkK]

You can write a query where one of it's fields is a subquery that counts the number of records that have common fields. This is a little tricky and looks something like this...

[FONT="Verdana"]SELECT Table1.FieldID, Table1.Field1, Table1.Field2, 
  (SELECT Count(*) AS SubCount 
  FROM Table1 AS Table2
  WHERE Table1.Field1 = Table2.Field1
    AND Table1.Field2 = Table2.Field2) AS TiedCount
FROM Table1
WHERE ...
ORDER BY Table1.Field1, Table1.Field2
[/FONT]

This query will run slowly for large datasets since it requeries the entire table for each record, but each record indicates how many other records in the same table match as per the WHERE clause in the subquery.

Or you can open a recordset on a more simple query, traverse each record, and count matching records in the same table...

[FONT="Verdana"]dim rst as dao.recordset
dim criteria as string
dim myCount as long

set rst = currentdb.openrecordset( _
  "SELECT FieldID, Field1, Field2 " & _
  "FROM Table1")
with rst
  do while not .eof
    criteria = "Field1 = " & !Field1 & " AND Field2 = " & Field2
    myCount = dcount("*", "Table1", criteria)
    if myCount > 0 then
[COLOR="Green"]      'this item has matching records in the same table as per criteria
      'process tied records here[/COLOR]
    end if
    .movenext
  loop
  .close
end with
[/FONT]

You could also write a query that calls a function to return a field value.

[FONT="Verdana"]
[COLOR="Green"]'this is your query text[/COLOR]
SELECT FieldID, Field1, Field2, MyCountFunction(Field1, Field2) As TiedCount FROM Table1

[COLOR="Green"]'this function appears in a standard module[/COLOR]
Public Function MyCountFunction(Value1 as Long, Value2 as Long) as Integer
  dim criteria as string
  criteria = "Field1 = " & Value1 & " AND Field2 = " & Value2
  MyCountFunction = DCount("*", "Table1", criteria)
End Function[/FONT]

They all ultimately do the same thing, which is count records in the same table that match specific criteria about the current record.
Cheers,

 

[gemma-the-husky]

or you can find dupolicates with two queries

query one, just select runner, and the lap times etc

query two, use query 1, make it a toals query, use count, and criteria >1

if this returns any records, then you have a probelm - but it doesnt tell you how to resolve it, which isnt easy for access

 

[The_Doc_Man]

Lagbolt's solution works, here's what I might do.

Let's say that table X's fields are [Score], [TB1], [TB2], [TB3] (and that's all that you have). Let's say those are ALL numeric. TB1, 2, 3 are tiebreakers. [ORDINAL] is an integer. [TIE] is a yes/no

Define query RANKX as

SELECT [ORDINAL], [TIE], [SCORE],[TB1],[TB2],[TB3] FROM X ORDER BY [SCORE],[TB1],[TB2],[TB3] ;

But now, write a little bit of VBA to run from any form or even from a macro. (If run from a macro, it has to be a function.) In the VBA, open the recordset and step through it one item at a time. I'm gonna be sloppy about syntax.

Public Sub RANKORDERX()

Dim ORDNL, TRUETIE, LSCORE, LTB1, LTB2, LTB3 as LONG
Dim RSX as DAO.RECORDSET

Set rsx = currentdb.openrecordset("rankx")

TRUETIE = 0
LSCORE={any impossible value that could never be a real score}
LTB1 = {impossible value}
LTB2 = {impossible value}
LTB3 = {impossible value}

rsx.movefirst
ordnl = 1
with rsx
while not rsx.eof do
    .edit
    if  ([score] = lscore) and ([tb1] = ltb1) and ([tb2]=ltb2) and ([tb3]=ltb3) then
      truetie = truetie + 1
      ![tie] = TRUE
    else
      truetie = 0
      ![tie] = FALSE
    endif
    ![ordinal] = ordnl - truetie
    ordnl = ordnl + 1
    lscore = ![score]
    ltb1 = ![tb1]
    ltb2 = ![tb2]
    ltb3 = ![tb3]
    .update
    .movenext
end loop
end with
rsx.close
End Sub

If this doesn't work, you might have to do a little tweaking, and I make no bets.

What it does is it takes your query, which does the rank-order sort, and scans it to assign ordinals - the rank. BUT if the internal TRUETIE counter is not zero, it takes back the count.

What you might see is, if the eight record tied the seventh exactly, both would have a rank order equal to 7. The seventh record because it wasn't a tie and TRUETIE is zero when the ordinal is stored. The eighth record is also ranked 7 because TRUETIE is 1 when it's ordinal is stored. Remember, the internal ordinal counter is counting records. Now if record 9 happens to ALSO tie, recordnumber 9 minus TRUETIE of 2 stores rankorder of 7. Also, the tie markers get set when the tie is detected.

Now let record 10 NOT tie record 9, but record 11 ties record 10. Same logic applies. When you show the records, you get

1 (no tie)
2 (no tie)
3 (no tie)
4 (no tie)
5 (no tie)
6 (no tie)
7 (tie)
7 (tie)
7 (tie)
10 (tie)
11 (tie)
12 etc. etc. etc.

 

[helmpost]

Doc Man,

Sorry for the delay in responding. Your code works in so far as it does, in VBA, what lagbolt's query did - great. However, when there is a true tie, it seems to be assigning the 'wrong' ORDINAL value in that it seems to assume that one is greater than the other - ie it assigns a 1 and a 2 with lower rankings correctly geting a 3 and 4 whereas it should assign two 1's and the lower rankings getting 3 and 4 respectively. I have probably fouled this up because the values in the tie column are all set to no ..

Here's my code and I've attached an 'after process' copy of the table to show what I mean. This is reallty great stuff and I'd like to pursue this further if you can bear with me. I'm taking my grandaughter to EuroDisney on Wednesday 12th but will be back the following Monday 16th.

Regards, Helmpost

-------------------------------

Public Sub rankorderx()

Dim RANKX As String
Dim rsx As DAO.Recordset
Dim ORDNL, TRUETIE, LSCORE, LTB1, LTB2, LTB3 As Long

RANKX = "select [membername], [ordinal], [tie], [score], [tb1], [tb2], [tb3] from x order by [score], [tb1], [tb2], [tb3];"

Set rsx = CurrentDb.OpenRecordset(RANKX)

TRUETIE = 0
LSCORE = 99
LTB1 = 99
LTB2 = 99
LTB3 = 99

rsx.MoveFirst
ORDNL = 1
With rsx
'while not rsx.eof do
Do While Not rsx.EOF

.Edit
If (score = LSCORE) And (tb1 = LTB1) And (tb2 = LTB2) And (tb3 = LTB3) Then
TRUETIE = TRUETIE + 1
![tie] = True
Else
TRUETIE = 0
![tie] = False
End If
![ordinal] = ORDNL - TRUETIE
ORDNL = ORDNL + 1
LSCORE = ![score]
LTB1 = ![tb1]
LTB2 = ![tb2]
LTB3 = ![tb3]
.Update
.MoveNext
Loop
End With
rsx.Close
End Sub

 

[helmpost]

It's the following morning in the UK.

I may have misunderstood the use of 'score'. Here's how I have used it.

I only need to do this claculation if there were ties in the original processing. Let us say that four people have all tied in 8th place. The tie break should try to determine which of the four is truly in 8th, 9th, 10th and 11th place. Thus in the table, all the 'score's have the value of '8'. In the 'ordinal' column, that's initially null and I hope that for each person, this will reflect their position after the tie break, ie, 1,2,3,4. I can then, in code add a zero to the score of the person achieving the first ordinal place (1) to create a true 8th place, a 1 to the person achieving the seond ordinal place (2) to create a true 9th place and so on. This seems to work fine except for the case where the tie cannot be resolved in which case the unresolved people would share a place.

I do apologize if I have failed to get this across or if I have misunderstood the use of 'score'. Very keen to work with you for a final solution.

Best Regards, Helmpost