Handling Ties in VBA

[helm]

Hi Folks,

I have an Access 2003 application handling dinghy racing results. I'm looking for a way to handle ties. On the current first recordset pass, the results are fine if there are no ties (orig points). If there are ties, then they need to be handled by a process that recognises that ties exist (elapsed time) and adds together the original values (Orig Points) and divides by the number of ties to create new values (Points Reqd). As in the table below.

Trust this makes sense and that you folks can suggest some suitable code ... with best regards, Brian


Record Elapsed time Orig Points Points Reqd

1 5.5 1 1
2 5.6 2 3
3 5.6 3 3
4 5.6 4 3
5 5.7 5 5
6 5.7 6 5
7 5.8 7 7
8 5.9 8 8

[Oralloy]

helm,

Are you trying to use just SQL, or are you using VBA for this?

Most importantly, what are your requirements? What sorts of information are you trying to extract from this database? It is pretty obvious that you are currently in the design phase, so let's discuss.

Finally, when you put down a data table, you should probably enclose it in CODE tags, or some such, so that we can read it easily. Like this:

 
This is your data table
 
Record  Elapsed time  Orig Points  Points Reqd
 
   1        5.5           1            1
   2        5.6           2            3
   3        5.6           3            3
 
etc... 
 

Cheers,
Oralloy

[ADezii]

Here is my Code Based approach that has been tested and appears to work quite well. The Logic is:

1. Create a Recordset consisting of Unique Elapsed Times.
2. Loop through this Recordset testing each Elapsed Time to see if there are Ties.
3. If there are Tie(s):
   1. Calculate the Total Original Points for these Ties.
   2. Calculate the actual Number of Ties.
   3. Calculate the new Value for Original Points using the Formula:
      

      'Notice use of Integer Division
      (Total Original Points \ Number of Ties)

   4. Update your Table (tblTest) with the Recalculated Values for Original Points for each Tie.
4. The actual Code, which has been tested is:
   

   Dim MyDB As DAO.Database
   Dim rsUniqueET As DAO.Recordset
   Dim intNumOfTies As Integer
   Dim intTotalOrigPoints As Integer
    
   Set MyDB = CurrentDb
    
   'Create a Recordset consisting of 'Unique' Elapsed Times
   Set rsUniqueET = MyDB.OpenRecordset("SELECT DISTINCT [Elapsed Time] FROM tblTest", dbOpenForwardOnly)
    
   With rsUniqueET
     Do While Not .EOF
       'For each Unique Elapsed Time, are there other Ties?
       intNumOfTies = DCount("*", "tblTest", "[Elapsed Time] = " & ![Elapsed Time])
         If intNumOfTies > 1 Then   'There are Ties
           'Calculate Total Original Points for the Ties
           intTotalOrigPoints = DSum("[Orig Points]", "tblTest", "[Elapsed Time] = " & ![Elapsed Time])
             'Update Points Required for all the Ties via provided Formula
             CurrentDb.Execute "UPDATE tblTest SET [Points Reqd] = " & (intTotalOrigPoints \ intNumOfTies) & _
                               " WHERE [Elapsed Time] = " & ![Elapsed Time], dbFailOnError
         End If
          .MoveNext
     Loop
   End With
    
   rsUniqueET.Close
   Set rsUniqueET = Nothing
    

5. Any questions, please feel free to ask.

P.S. - There may be a SQL approach that is more efficient and practical, but SQL is not my area of expertise.

[helm]

Hi Adezii,

That's brilliant - finds ties and updates records, Can we take this a step further please? In your solution, all the ties are given the same value - great - but there are some values that would not be integers - if for example there were ties at [orig points] 5 and 6, the values to be assigned [points reqd]would be 5+6/2 = 5.5

Thought I could change through from integer values to single but results still come out as integers ...

Regards, Helm

[zmbd]

You should be able to do this with a ranking query:
How to Rank Records Within a Query

Then there is good-ole Allen Brown:
Ranking or numbering records

[ADezii]

If you wish to keep this approach:

1. Change the Data Type of the [Orig Points] Field in your Table to SINGLE.
2. Change the SQL Statement used to Update your Table to:
   

   CurrentDb.Execute "UPDATE tblTest SET [Points Reqd] = " & _
                                Round((intTotalOrigPoints / intNumOfTies), 2) & _
                               " WHERE [Elapsed Time] = " & ![Elapsed Time], dbFailOnError

3. This will Round the Results to 2 Decimal Places.
4. Make sure that you change the Integer Division Operator (\) to Division (/).
5. As stated earlier, a SQL approach would be more efficient.

[Oralloy]

helm,

You've obviously found the help you need.

It sounds like you are having difficulty storing the computed values. Remember that even though you calculate using floating-point, if the column you store the data in is an integer type, the digits after the decimal point will be discarded (lost).

Ok, that's my two-cents worth.

Good luck with your project.

Oralloy

[helm]

A big thank you to ADezzi - I didn't know that there weretwo division operators! Thanks too for those SQL references - I'm going to look at those in more detail. As you say, an SQL approach is great if you can work out (or find out) how to do it.

Best Regards, helm

[Rabbit]

If I understand correctly, you can join this query to the original table on the [Elapsed time] field.

SELECT
   [Elapsed time],
   SUM([Orig Points]) / COUNT(*) AS [Points Reqd]
FROM tblTest
GROUP BY [Elapsed time]

[ADezii]

@helm:
I must say that Rabbitt's solution is much more elegant, efficient, and professional. I would strongly suggest that you adapt his approach. The only change that you may wish to make is to control the number of Decimal Places displayed, as in:

SELECT 
[Elapsed time], 
Round(SUM([Orig Points]) / COUNT(*),2) AS [Points Reqd] 
FROM tblTest 
GROUP BY [Elapsed time]

[helm]

Thank you Adezii and Rabbit,

The recordset I'm using has been through a range of activities before it gets to this (ties) point in the overall processing - I have quite a few 'with recordset' sections to test and answer various things ... I could now drop the recordset into a table and use SQL to work out the ties ... I wonder though whether it's possible to run SQL against a recordset rather than a table - sorry if sounds a bit of a naïve question (I'm an amateur dabbler as opposed to a professional programmer)-
just so glad to have an answer, at last to the 'ties' problem!

Regards, helm

[Rabbit]

You can incorporate the SQL into the SQL you use to create the recordset.

[helm]

Hi Rabbit and Adezzi

Back from my hols ...

From some tests looks like I can't Dcount on a recordset - which is a pity since I have it right there ready to go. I could close the recordset and then run Adezzi's solution against the original data table ... but did you both believe that I could achieve the same thing (identify ties and update the table with 'just' an SQL update query)?

Thanks for your continuing interest and regards

Helm

[Rabbit]

If you wanted to update the table you could. But there's no need to do so when you can caluclate it dynamically. And in fact, it's recommended that you not store what can be calculated unless there's very good reason to.

I'm not sure what you mean by the DCount, there's no need to use a DCount with my solution. And I'm not sure what you mean by recordset because the SQL for any recordset can be put in a query and you can use a DCount on a query. But then again, I don't see any need to use a DCount in the first place.

[helm]

I probably have not been very clear. Following data entry into a form, I open a dynaset and undertake quite a lot of data processing (2 A4 pages in fact) which includes the calculation of 'points'. The value of the points is fine just as long as there are no ties (where [elapsed time] is the same for two or more records). ADezii showed me a way using VBA to calculate my tie values using data from a test table. And sure thing, it worked.

In was working on the ADezzi approach when Rabbit suggested a wholly SQL approach.

If using a VBA approach, I've found that I can't just replace a source table with a recordset using the DCount/Dsum methods so guess that I need to separately open my source table, do some tie calcs and close the table.

If the issue can be solved using wholly SQL then happy to adopt that way forward ... assume I would need something like UPDATE table, SET, WHERE ... and so on) ...

Regards, Helm

[Rabbit]

My point is that there's no need to replace/update anything. Just run the query as needed and everything will be up to date without having to save any additional data.

When you save calculated data, you run the risk of becoming out of sync with the rest of the data. If you instead run a query whenever the data is needed, it will always be correct and up to date.