473,839 Members | 1,473 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
15 1635
26 New Member
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
Jun 2 '13 #11
12,516 Recognized Expert Moderator MVP
You can incorporate the SQL into the SQL you use to create the recordset.
Jun 2 '13 #12
26 New Member
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

Jun 11 '13 #13
12,516 Recognized Expert Moderator MVP
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.
Jun 11 '13 #14
26 New Member
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
Jun 11 '13 #15
12,516 Recognized Expert Moderator MVP
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.
Jun 11 '13 #16

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: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
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: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
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: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.