Calculating Problem

  • Thread starter Thread starter NewPCConsultant
  • Start date Start date
N

NewPCConsultant

I have inherited a database that calculates percentages based on whether a
checkbox = true or false. The problem that I am having is that I have been
tasked to change the values for certain boxes, and now when I go to calculate
the totals, some of the totals are showing a 1 meaning 100% and others are
showing a .8 or 80% when all boxes are checked. Here is the sql for the
query. Can someone please help?

AssembScore:
([tblAssembly]![AssmCor]*-20+[tblAssembly]![AssmJkt]*-10+[tblAssembly]![AssmAppC]*-10+[tblAssembly]![AssmSig]*-20+[tblAssembly]![AssmCorAmt]*-10+[tblAssembly]![AssmCorSex]*-10+[tblAssembly]![AssmDOB]*-5+[tblAssembly]![AssmAddCor]*-10+[tblAssembly]![AssmPremCor]*-5)/100

Thanks in advance
 
NewPCConsultant said:
I have inherited a database that calculates percentages based on whether a
checkbox = true or false. The problem that I am having is that I have been
tasked to change the values for certain boxes, and now when I go to calculate
the totals, some of the totals are showing a 1 meaning 100% and others are
showing a .8 or 80% when all boxes are checked. Here is the sql for the
query. Can someone please help?

AssembScore:
([tblAssembly]![AssmCor]*-20+[tblAssembly]![AssmJkt]*-10+[tblAssembly]![AssmAppC]*-10+[tblAssembly]![AssmSig]*-20+[tblAssembly]![AssmCorAmt]*-10+[tblAssembly]![AssmCorSex]*-10+[tblAssembly]![AssmDOB]*-5+[tblAssembly]![AssmAddCor]*-10+[tblAssembly]![AssmPremCor]*-5)/100

Thanks in advance
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Be sure to make true a -1 and false a 0 (zero).

Sometimes, with forms, the fields may show one thing, but a query,
supposedly based on the fields data is different because the data hasn't
been saved to the table & the query only reads the table. Be sure the
data has been saved before running the query.

To test the query to see that what the values are just put the field
names (AssmCor, AssmJkt, etc.) in the design grid and test run the
query. If any of them are false then the AssembScore will not be 100%.

The AssembScore formula just subtracts the indicated value from 100% -
so if AssmCor was False and all other fields were True the pct would be
80%. You'll have to add the results to find out what's missing.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR5W2BYechKqOuFEgEQLeRgCg8V+Uv60JIHMuvg3ua6zRcuuugq4An1sx
UjlSGy7FaSRoAbEOqG0QH1Cp
=u4SH
-----END PGP SIGNATURE-----
 
I must know is it :
([tblAssembly]![AssmCor]*(-20+[tblAssembly]![AssmJkt])*-...
Or
([tblAssembly]![AssmCor]*-20(+[tblAssembly]![AssmJkt])*-...
Or
([tblAssembly]![AssmCor]*-20+([tblAssembly]![AssmJkt])*-...

It makes a difference where you put your parenthesis
 
Is the proper form:
([tblAssembly]![AssmCor]*(-20+[tblAssembly]![AssmJkt])*-. . .
Or
([tblAssembly]![AssmCor]*-(20+[tblAssembly]![AssmJkt])*-. . .
Or
([tblAssembly]![AssmCor]*-20+([tblAssembly]![AssmJkt])*-. . .

Where you put your parenthesis will make a difference in the outcome.
 
Thanks for everyones help. I found out that the problem was not in the
calculations, but in the design, inherited when I got the database, of the
report that was generated by the query.

MGFoster said:
NewPCConsultant said:
I have inherited a database that calculates percentages based on whether a
checkbox = true or false. The problem that I am having is that I have been
tasked to change the values for certain boxes, and now when I go to calculate
the totals, some of the totals are showing a 1 meaning 100% and others are
showing a .8 or 80% when all boxes are checked. Here is the sql for the
query. Can someone please help?

AssembScore:
([tblAssembly]![AssmCor]*-20+[tblAssembly]![AssmJkt]*-10+[tblAssembly]![AssmAppC]*-10+[tblAssembly]![AssmSig]*-20+[tblAssembly]![AssmCorAmt]*-10+[tblAssembly]![AssmCorSex]*-10+[tblAssembly]![AssmDOB]*-5+[tblAssembly]![AssmAddCor]*-10+[tblAssembly]![AssmPremCor]*-5)/100

Thanks in advance
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Be sure to make true a -1 and false a 0 (zero).

Sometimes, with forms, the fields may show one thing, but a query,
supposedly based on the fields data is different because the data hasn't
been saved to the table & the query only reads the table. Be sure the
data has been saved before running the query.

To test the query to see that what the values are just put the field
names (AssmCor, AssmJkt, etc.) in the design grid and test run the
query. If any of them are false then the AssembScore will not be 100%.

The AssembScore formula just subtracts the indicated value from 100% -
so if AssmCor was False and all other fields were True the pct would be
80%. You'll have to add the results to find out what's missing.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR5W2BYechKqOuFEgEQLeRgCg8V+Uv60JIHMuvg3ua6zRcuuugq4An1sx
UjlSGy7FaSRoAbEOqG0QH1Cp
=u4SH
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top