Solved

Total for check marks add to 100 but not getting 100%

Posted on 2012-12-27
9
292 Views
Last Modified: 2013-01-05
Hello experts these fields are check marks everytime you click on one it is equavalent to certain percenatage
The total is 100 but I am getting 555.5% with this code that is in a query. Can somebody tell me why it doesnt equal 100% when totaled.

I tried putting all the numbers in percentage format and that didnt work either for example .02,.08.


Percentages: Format(Abs(([tblTempleFuelcellOnly]![Induction]*8+[tblTempleFuelcellOnly]![Defuel]*2+[tblTempleFuelcellOnly]![Turret ull]*5+[tblTempleFuelcellOnly]![FuelCellRemoved]*10+[tblTempleFuelcellOnly]![BFCSProvision]*2+[tblTempleFuelcellOnly]![BFCSInstall]*36+[tblTempleFuelcellOnly]![TurretInstall]*18+[tblTempleFuelcellOnly]![DriversSeatSpacer]*1+[tblTempleFuelcellOnly]![GunnersSeatStop]*1+[tblTempleFuelcellOnly]![CEPRetrofit]*2+[tblTempleFuelcellOnly]![25MM HotBox]*2+[tblTempleFuelcellOnly]![E1GroundInsert]*2+[tblTempleFuelcellOnly]![AFESCEPSwitchGuard]*1+[tblTempleFuelcellOnly]![ReliefHoleExtinguisherGuard]*1+[tblTempleFuelcellOnly]![EERHandleMod]*3+[tblTempleFuelcellOnly]![QA/QC]*2+[tblTempleFuelcellOnly]![OutDuction]*3 +[tblTempleFuelcellOnly]![Complete]*1)/18),"00.0%")
0
Comment
Question by:gigifarrow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38723829
At the most basic level the "Factor" (the numbers you are multiplying by in each segment) will determine the end result.

So if the first segment:
[tblTempleFuelcellOnly]![Induction]
...evaluates to 100,
...Then 100*8=800

So you can see here that this formula may not be the best technique to get values that will always add up to100%.

The formula to get a percentage of each part to the whole (100%) is
each number divided by the Total:
Amount
459
846
121
Here the total is: 1426
So,
459/1426=32.19%
846/1426=59.33%
121/1426=8.49%

So, the sum of the percents will always be 100%
32.19%
59.33%
8.49%

Make sense?

It would seem that your formula here would have to do something similar, so you will have to explain what each of those "factors" represents...

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38723865
sampleSample

SELECT YourTable.RecordID, YourTable.Field1, YourTable.Field2, YourTable.Field3, [Field1]+[Field2]+[Field3] AS SumFields, [Field1]/([Field1]+[Field2]+[Field3]) AS PerField1, [Field2]/([Field1]+[Field2]+[Field3]) AS PerField2, [Field3]/([Field1]+[Field2]+[Field3]) AS PerField3, ([Field1]/([Field1]+[Field2]+[Field3]))+([Field2]/([Field1]+[Field2]+[Field3]))+([Field3]/([Field1]+[Field2]+[Field3])) AS 100
FROM YourTable;
Database1.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38724024
This illustrate the issue of trying to do calculations across fields
Your formulas become long:
F1+F1+F3................
You have to allow for nulls
NZ(F1)+NZ(F1)+NZ(F3)................
It becomes hard to read, and by extension, ...hard to troubleshoot
...ect

Not trying to suggest that you change your existing design here, ...only that your existing design makes doing these things difficult.

Another example:, IMHO, your "factors" should be store somewhere (in a/the table) and not simply hardcoded in the query only.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:gigifarrow
ID: 38724138
Thanks for your time and your help!


These fields represent check marks I have 18. When a job is finished they check off what they have done and then it is worth a certain percentage.
So i cant put amount in the check mark fields. Least I dont think so. How can I just use what have? Do i have to divide the percentage its worth by some number that will total to 100% to get the correct answer?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38724215
<How can I just use what have>
You can, ...but you may run into the more problems if you don't understand the ramifications of staying with your existing design...
Then you will be back asking even more questions....
1. For example: will there *Always* only be 18 selections?
If so then you can harcdcode the 18,
If not then this system need to get even more complex and "Count" the number of checkboxes.

2. Will the factors always be the same?
Because if they ever change, you will have to open this query *every* time and edit the factors
(The same goes for the count of the checkboxes if it is hardcoded at 18).

If it were me, I would redesign this to be one field (Task), and have multiple rows for each yes/no, ..instead of multiple columns.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38724439
Also, ...note that the sum of all your factors exceeds 100
(FWIW...)
0
 

Author Comment

by:gigifarrow
ID: 38727481
Everything will be the same. So how would I take my formula and make it work?
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 38727606
Have you tried just dividing by a 100 instead of 18?

If all check values are 1 and multiplied by the factors add up to a 100 it seems right; the result of 100 / 18 = 5.55 (or 555.5%), whereas 100 / 100 obviously equals 1 (or 100%)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727811
gig,

Investigate robert_schutt's post above.

Like I said, perhaps I missed something simple, or am not understanding something...
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question