473,839 Members | 1,471 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Handling Ties in VBA

26 New Member
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
May 30 '13 #1
15 1634
988 Recognized Expert Contributor

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:
Expand|Select|Wrap|Line Numbers
  2. This is your data table
  4. Record  Elapsed time  Orig Points  Points Reqd
  6.    1        5.5           1            1
  7.    2        5.6           2            3
  8.    3        5.6           3            3
  10. etc... 
May 30 '13 #2
8,834 Recognized Expert Expert
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:
      Expand|Select|Wrap|Line Numbers
      1. 'Notice use of Integer Division
      2. (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:
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database
    2. Dim rsUniqueET As DAO.Recordset
    3. Dim intNumOfTies As Integer
    4. Dim intTotalOrigPoints As Integer
    6. Set MyDB = CurrentDb
    8. 'Create a Recordset consisting of 'Unique' Elapsed Times
    9. Set rsUniqueET = MyDB.OpenRecordset("SELECT DISTINCT [Elapsed Time] FROM tblTest", dbOpenForwardOnly)
    11. With rsUniqueET
    12.   Do While Not .EOF
    13.     'For each Unique Elapsed Time, are there other Ties?
    14.     intNumOfTies = DCount("*", "tblTest", "[Elapsed Time] = " & ![Elapsed Time])
    15.       If intNumOfTies > 1 Then   'There are Ties
    16.         'Calculate Total Original Points for the Ties
    17.         intTotalOrigPoints = DSum("[Orig Points]", "tblTest", "[Elapsed Time] = " & ![Elapsed Time])
    18.           'Update Points Required for all the Ties via provided Formula
    19.           CurrentDb.Execute "UPDATE tblTest SET [Points Reqd] = " & (intTotalOrigPoints \ intNumOfTies) & _
    20.                             " WHERE [Elapsed Time] = " & ![Elapsed Time], dbFailOnError
    21.       End If
    22.        .MoveNext
    23.   Loop
    24. End With
    26. rsUniqueET.Close
    27. 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.
May 30 '13 #3
26 New Member
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
May 30 '13 #4
5,501 Recognized Expert Moderator Expert
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
May 31 '13 #5
8,834 Recognized Expert Expert
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:
    Expand|Select|Wrap|Line Numbers
    1. CurrentDb.Execute "UPDATE tblTest SET [Points Reqd] = " & _
    2.                              Round((intTotalOrigPoints / intNumOfTies), 2) & _
    3.                             " 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.
May 31 '13 #6
988 Recognized Expert Contributor

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.

May 31 '13 #7
26 New Member
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
May 31 '13 #8
12,516 Recognized Expert Moderator MVP
If I understand correctly, you can join this query to the original table on the [Elapsed time] field.
Expand|Select|Wrap|Line Numbers
  2.    [Elapsed time],
  3.    SUM([Orig Points]) / COUNT(*) AS [Points Reqd]
  4. FROM tblTest
  5. GROUP BY [Elapsed time]
May 31 '13 #9
8,834 Recognized Expert Expert
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. [Elapsed time], 
  3. Round(SUM([Orig Points]) / COUNT(*),2) AS [Points Reqd] 
  4. FROM tblTest 
  5. GROUP BY [Elapsed time]
Jun 1 '13 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: WSeeger | last post by:
When creating a new class, is it encouraged to always include error handling routines within your LET and GET procedures? It's seems that most text books never seem to include much about error handling within classes. Just hoping to hear some programmer's thoughts on error handling.
by: Hans-Joachim Widmaier | last post by:
Hi all. Handling files is an extremely frequent task in programming, so most programming languages have an abstraction of the basic files offered by the underlying operating system. This is indeed also true for our language of choice, Python. Its file type allows some extraordinary convenient access like: for line in open("blah"): handle_line(line)
by: Master of C++ | last post by:
Hi, I am an absolute newbie to Exception Handling, and I am trying to retrofit exception handling to a LOT of C++ code that I've written earlier. I am just looking for a bare-bones, low-tech exception handling mechanism which will allow me to pass character information about an error and its location from lower-level classes. Can you please critique the following exception handling mechanism in terms of my requirements ?
by: ED | last post by:
I currently have vba code that ranks employees based on their average job time ordered by their region, zone, and job code. I currently have vba code that will cycle through a query and ranks each employee based on their region, zone, job code and avg job time. (See code below). My problem is that I do not know how to rank the ties. Right now if two people have the same avg time one will be ranked 3rd and the other ranked 4th. I would...
by: Anthony England | last post by:
Everyone knows that global variables get re-set in an mdb when an un-handled error is encountered, but it seems that this also happens when the variable is defined as private at form-level. So if "global variables get re-set" doesn't tell the whole story, then what does? ***please note*** I'm not looking for a solution - I'm looking for a more detailed description of what happens when an un-handled error occurs - possibly with help file...
by: Stefan Johansson | last post by:
Hi all I'am moving from Visual Foxpro and have a question regarding "best practice" error handling in vb .net. In VFP I have always used a "central" error handling object in order to have a easy and reusable way of handling all errors in a program. The VB 6 coding examples I have seen there has always been error handling code in each program module.
by: Al Williams | last post by:
Hi, I have error handling in place throughout my application. I also start the application wrapped in error handling code to catch any unexpected exceptions (i.e. exceptions that occur where I haven't placed error handling code). When I run my app from the IDE, the unhandled errors are caught by the error handling code in my Sub Main routine and the error details are logged to a text file and optionally e-mailed to me for follow-up.
by: Gustaf | last post by:
I'm confused about structured error handling. The following piece of code is a simplification of a class library I'm working on. It works, and it does what I want, but I'm still not convinced that I have been doing it right. I think I overdo it. Please have a look: -- using System; using System.IO;
by: pargat.singh | last post by:
Hi Everyone: I have a C# program which loop through No. of files. In UAT i had medium size files and every thing goes ok but in Production files are big and i notice that my program is very slow and it's taking 100% CPU time. Is there anyway i can improve the performance. Something like Do Events etc. Any suggestion is much appreciated.
by: Lysander | last post by:
Thought I would give something back with a few articles. This article is a bit of code to add error handling. When I have time, I want to write articles on multilingual databases, and Access Security, but I'll start with something short and simple This code was written in Access 2003 but should be valid in Access 2000 By default, when you start a new module, either in a form or report, or a global module, Access does not declare Option...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.