doing math on summed totals

  • Thread starter Thread starter Repent
  • Start date Start date
R

Repent

John;

now that I have that working....

I want to find the waste% using the sums available. For example, I
have summed up colums for LF Run, LF Produced, and waste.

I want the waste% based on dividing the summed totals of waste by the
summed totals of LF Run.

I tried the code of =Sum([waste])/Sum([LFrun]) but when i run that it
asks for a value, like a query parameter. I just want it to take the
summed value for waste and divide it by the summed value for LFrun.

I thought the code i show above would do that. What do I have wrong?

chris




John;

thanks for the info and the leads! I changed the type to "numbers"
instead of "text" in the field type in the table (as I am pulling the
data from a table field). That did the trick.
I could then see that "sum" was unghosted and available to me.
I could then also create groups using the group & sort wizards and get
the totals I am looking for.

Once again, thanks for the info. I wracked my brain way too long
yesterday on this.

chris



The control is a text box. The control has a control source and usually that
is a FIELD in a TABLE or QUERY.

What is the field type in the table? If it is text then you cannot sum it.
If it is a number then you should be able to sum it.

If you are using the table directly, you have to change the field type. If
you are using a query as the source you can use VAL function to change the
text to a number type. You would need a calculated column to do this. OR
open the report in design view, change the control name to txtLFRun and then
change its source to
=Val([Lf Run] & "")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The property sheet for "LF Run" shows that it is a text box. it
contains numbers. Is this t he problem? How can I fix that?
Click to expand...
Click to expand...
 
Repent said:
I want to find the waste% using the sums available. For example, I
have summed up colums for LF Run, LF Produced, and waste.

I want the waste% based on dividing the summed totals of waste by the
summed totals of LF Run.

I tried the code of =Sum([waste])/Sum([LFrun]) but when i run that it
asks for a value, like a query parameter. I just want it to take the
summed value for waste and divide it by the summed value for LFrun.

I thought the code i show above would do that. What do I have wrong?
thanks for the info and the leads! I changed the type to "numbers"
instead of "text" in the field type in the table (as I am pulling the
data from a table field). That did the trick.
I could then see that "sum" was unghosted and available to me.
I could then also create groups using the group & sort wizards and get
the totals I am looking for.
Click to expand...


I think you spelled LF Run two different ways. Whichever
one you are prompted to enter is spelled wrong (missing
space?)
 
you're right about the space. What a knucklehead I am.

When I put the space in where it belongs, my control source for the
text box is: =Sum([Waste%])/Sum([LF RUN]) and that no longer gives
errors but all it gives is the answer of 0.00% in every instance.

I'm wondering if that's because the answers for waste% is blank in the
table because I have a query that populates these results to a report.

when i look at the record source for the report it is the query
though....





Repent said:
I want to find the waste% using the sums available. For example, I
have summed up colums for LF Run, LF Produced, and waste.

I want the waste% based on dividing the summed totals of waste by the
summed totals of LF Run.

I tried the code of =Sum([waste])/Sum([LFrun]) but when i run that it
asks for a value, like a query parameter. I just want it to take the
summed value for waste and divide it by the summed value for LFrun.

I thought the code i show above would do that. What do I have wrong?
thanks for the info and the leads! I changed the type to "numbers"
instead of "text" in the field type in the table (as I am pulling the
data from a table field). That did the trick.
I could then see that "sum" was unghosted and available to me.
I could then also create groups using the group & sort wizards and get
the totals I am looking for.
Click to expand...


I think you spelled LF Run two different ways. Whichever
one you are prompted to enter is spelled wrong (missing
space?)
Click to expand...
 
Repent said:
. . . my control source for the text box is:
=Sum([Waste%])/Sum([LF RUN])
all it gives is the answer of 0.00% in every instance.

I'm wondering if that's because the answers for waste% is blank in the
table because I have a query that populates these results to a report.

when i look at the record source for the report it is the query
though....
Click to expand...


I need more information because that doesn't make sense to
me, If the field has no values in it, what is Sum supposed
to operate on??
 
Doesn't make sense to me either. Course I'm a relative newbie so that
doesn't count.....forgive me if I use the wrong language.

in the table, the waste and waste% have no data because they are
calculated fields that calc when I run a query.

In the table, there are fields for both LF Run and LF Produced.
A certain amount of LF is run, then when waste is taken away from the
process, you then have a certain amount that we call LF Produced.

LF Run - LF Produced = good product
the difference between LF Run and LF Produced is the scrap or waste

if I have three operators, each on their own equipment, producing
finished goods, they will have figures to show LF Run, LF Produced,
waste, and waste%.

We take the amount of waste and divide by the LF Run to get the
waste%.

I want to add up seperately the LF Run, LF Produced, and waste. I
then want to divide the total waste by the total LF Run to get my
total waste%


hope that helps to clarify?



Repent said:
. . . my control source for the text box is:
=Sum([Waste%])/Sum([LF RUN])
all it gives is the answer of 0.00% in every instance.

I'm wondering if that's because the answers for waste% is blank in the
table because I have a query that populates these results to a report.

when i look at the record source for the report it is the query
though....
Click to expand...


I need more information because that doesn't make sense to
me, If the field has no values in it, what is Sum supposed
to operate on??
Click to expand...
 
whats wierd also is that if i change the "/" into a "+", a "-", or a
"*", then the math results are other than 0. When I change it back to
a "/", I get a "0.00%" answer.


OH GREAT! WHILE TYPING OUT THIS INFO I REALIZED THAT THE WHOLE TIME I
KEPT USING "WASTE%" INSTEAD OF "WASTE" IN MY FORMULA. THE RESULT I
WAS LOOKING FOR WAS WASTE% SO I CANNOT START OUT MY FORMULA THAT WAY!!





Doesn't make sense to me either. Course I'm a relative newbie so that
doesn't count.....forgive me if I use the wrong language.

in the table, the waste and waste% have no data because they are
calculated fields that calc when I run a query.

In the table, there are fields for both LF Run and LF Produced.
A certain amount of LF is run, then when waste is taken away from the
process, you then have a certain amount that we call LF Produced.

LF Run - LF Produced = good product
the difference between LF Run and LF Produced is the scrap or waste

if I have three operators, each on their own equipment, producing
finished goods, they will have figures to show LF Run, LF Produced,
waste, and waste%.

We take the amount of waste and divide by the LF Run to get the
waste%.

I want to add up seperately the LF Run, LF Produced, and waste. I
then want to divide the total waste by the total LF Run to get my
total waste%


hope that helps to clarify?



Repent said:
. . . my control source for the text box is:
=Sum([Waste%])/Sum([LF RUN])
all it gives is the answer of 0.00% in every instance.

I'm wondering if that's because the answers for waste% is blank in the
table because I have a query that populates these results to a report.

when i look at the record source for the report it is the query
though....
Click to expand...


I need more information because that doesn't make sense to
me, If the field has no values in it, what is Sum supposed
to operate on??
Click to expand...
Click to expand...
 
Repent said:
whats wierd also is that if i change the "/" into a "+", a "-", or a
"*", then the math results are other than 0. When I change it back to
a "/", I get a "0.00%" answer.


OH GREAT! WHILE TYPING OUT THIS INFO I REALIZED THAT THE WHOLE TIME I
KEPT USING "WASTE%" INSTEAD OF "WASTE" IN MY FORMULA. THE RESULT I
WAS LOOKING FOR WAS WASTE% SO I CANNOT START OUT MY FORMULA THAT WAY!!
Click to expand...


Right. Using that will calculate very small numbers that
probably will display as 0.

Did using Waste resolve your question or are you still
having a problem?
 
Back
Top