gigifarrow

asked on

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

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%")

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(([tblTempleFuel

Sample

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

SELECT YourTable.RecordID, YourTable.Field1, YourTable.Field2, YourTable.Field3, [Field1]+[Field2]+[Field3]

FROM YourTable;

Database1.accdb

This illustrate the issue of trying to do calculations

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.

**across**fieldsYour 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.

ASKER

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?

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?

<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

2. Will the factors

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

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

Also, ...note that the sum of all your factors exceeds 100

(FWIW...)

(FWIW...)

ASKER

Everything will be the same. So how would I take my formula and make it work?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

gig,

Investigate robert_schutt's post above.

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

Investigate robert_schutt's post above.

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

So if the first segment:

[tblTempleFuelcellOnly]![I

...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