473,698 Members | 2,186 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Distribute numbers evenly across records

1 New Member
Hi all,

I am a newby to this forum. I am making modifications to an existing database. The structure is basicall one primary record in a main table with many associated records in a second table. The associated records has a field in it called hours and is formated as a number. I need to update this field based on criteria in a text box which is also a number. In all the the idea is to take the text box number which represent cuts to the hours field but these cuts need to be spread over all of the records in the same ratio as the original hours. Here goes... example: 5 records have a total of 21 hours. each record obviously hs a different "hours" number. Record 1 has 3; record 2 has 1; etc until all five add to 21. If I wanted to cut the total of 21 hours by 11 hours I have the following formula in an update query: 11divided by 21 * the number of hours inf the hours field. It works great until I have to round the numbers. Sometimes (more often than not) the calculation does not work out evenly and I always end up not getting to the new number, (21 to 10). Every rounding combination that I have tried does not tie up the left over fraction of numbers. Hopefully I have explained this enough for someone to understand. Please if anyone has any ideas, please let me know how to control the calulation so that I end up with spreading these "cut hours" across all the records in the same percentage of the original.

Thanks
Kevin
Aug 21 '07 #1
1 2883
Jim Doherty
897 Recognized Expert Contributor
Hi all,

I am a newby to this forum. I am making modifications to an existing database. The structure is basicall one primary record in a main table with many associated records in a second table. The associated records has a field in it called hours and is formated as a number. I need to update this field based on criteria in a text box which is also a number. In all the the idea is to take the text box number which represent cuts to the hours field but these cuts need to be spread over all of the records in the same ratio as the original hours. Here goes... example: 5 records have a total of 21 hours. each record obviously hs a different "hours" number. Record 1 has 3; record 2 has 1; etc until all five add to 21. If I wanted to cut the total of 21 hours by 11 hours I have the following formula in an update query: 11divided by 21 * the number of hours inf the hours field. It works great until I have to round the numbers. Sometimes (more often than not) the calculation does not work out evenly and I always end up not getting to the new number, (21 to 10). Every rounding combination that I have tried does not tie up the left over fraction of numbers. Hopefully I have explained this enough for someone to understand. Please if anyone has any ideas, please let me know how to control the calulation so that I end up with spreading these "cut hours" across all the records in the same percentage of the original.

Thanks
Kevin
Kevin

What is the data type of the number you are working with? look at the DECIMAL datatype which will give you the precison you seek. In addition you might want to look at creating a formula in a 'query' as a column so that the return values can be 'calculated' rather than stored?

Jim
Aug 23 '07 #2

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

Similar topics

8
14727
by: | last post by:
I have to write a program that displays all of the perfect numbers less than 1000. I know I need 2 for loops and at least one if statement. What I need to know is, is there any method in Java to test if a number is perfect? Thanks.
2
21817
by: webmaker | last post by:
I have a table that is a menu containing 10 items horizontally. I want it to be able to stretch across the screen sizing itself appropriately. I have each item in a separate table cell and I was hoping I could have the cells spaced evenly-somehow. I tried adding 10% to the CSS, but it is still spacing them strangley. The longer words have bigger cells of empty space and the smaller words have no extra width at all. I'm working in IE6, but...
24
5898
by: Stavros Christoforou | last post by:
Hello everyone, I was wondering if someone could help me with an issue I have in C++. I want to select random points within the volume of a sphere. I know how to get random numbers using srand() and rand(), but have no idea how to do that within a more complicated geometry. Any help would be greatly appreciated.. Regards Stavros
6
2126
by: Damon Grieves | last post by:
Hi I just want to be sure I understand how the Access client works. If I have an Access back end with a million records on a server and an Access client. If the client is installed on the users pc and run by the user...does Access drag the whole million records across the LAN when we call up one record ie filters for that record locally? If the client is on the same server as the back end, and the user starts up the client on the server...
5
2164
by: sparks | last post by:
We have a database with 1600+ records in it. They are linked across 7 tables by an ID type autonumber. Now we want(not I want LOL) to remove the first 1200 records no problem but they also want the ID to be reset to 1,2,3 ... this would mean that record 1234=1 1235=2 With the id being linked across 7 tables with relationships...one to one and some with one to many..can this be done without messing up the links?
0
1109
by: PromisedOyster | last post by:
I have an ASP menu (.NET 2.0) that is populated from the sitemap (see below for definition). However, I notice that the elements in the menu are not spaced evenly across. Is there any way to enforce this or is there something wrong with the way I am defining the menu?????? <asp:Menu ID="menu" runat="server" DataSourceID="SiteMapDataSource" Orientation="Horizontal" Width="800px"
5
12432
by: Hopp Family | last post by:
Here's the problem. I want to display a known number of images, evenly spaced out across the width of a div. (The images are not all the same width; the centers should be evenly spaced.) The div changes width with the browser, so the layout needs to be fluid. If the div narrows too much, the images should wrap; in this event, the images on each row should be evenly spaced (the spacing can vary from row to row). I can't figure out how to...
6
3758
by: devoreband | last post by:
hey i wrote all my code but i need to print out only the even numbers so is there any predefined function i can use or what can structure i should be following? ........ 16 for(int r = 0; r < 10; r++) { 17 for(int c = 0; c < 10; c++) { 18 cout << setw(5) << setfill(' ') << r*c; 19 } // end for c 20 cout << endl;
13
2804
by: Peter Oliphant | last post by:
I would like to be able to create a random number generator that produces evenly distributed random numbers up to given number. For example, I would like to pick a random number less than 100000, or between 0 and 99999 (inclusive). Further, the I want the range to be a variable. Concretely, I would like to create the following method: unsigned long Random( unsigned long num )
0
8674
marktang
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...
0
8604
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,...
1
8895
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,...
0
7728
agi2029
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...
0
5860
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();...
0
4369
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...
0
4619
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3046
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
2
2330
muto222
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.