doing math on summed totals

Repent · External Since: Nov 05, 2009 Posts: 5

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

On Wed, 04 Nov 2009 12:40:17 -0500, John Spencer <spencer.TakeThisOut@chpdm.edu>
wrote:

>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
>
>Repent wrote:
>> 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?
>>
>>
>>
>>
>> On Tue, 03 Nov 2009 19:09:42 -0500, John Spencer <spencer.TakeThisOut@chpdm.edu>
>> wrote:
>>
>>> As a guess Access believes that LF Run is not a number field. Is it by chance
>>> a text field that contains numbers or a calculated field that you have
>>> formatted with the format function. Format changes number type to string type.
>>>
>>> John Spencer
>>> Access MVP 2002-2005, 2007-2009
>>> The Hilltop Institute
>>> University of Maryland Baltimore County
>>>
>>> Repent wrote:
>>>> I have a report that is based on several tables and lookups.
>>>>
>>>> when I'm in layout view and I select Group & Sort from the Grouping &
>>>> Totals tab I can Group my records by the "productiondate" field then
>>>> when I select the Totals dropdown in the Group & Sort feature, select
>>>> the field of "LF Run" where it says Total On, the "type" changes from
>>>> "Sum" to only show the type of "Count values" or "Count Records". The
>>>> Sum, along with average, min, max, etc are ghosted and not available.
>>>>
>>>> There must be something wrong with my tables or something to make
>>>> these options not available.
>>>>
>>>> Can anyone help or lend some insight?
>>>>

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

Repent wrote:
>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.

I think you spelled LF Run two different ways. Whichever
one you are prompted to enter is spelled wrong (missing
space?)

--
Marsh
MVP [MS Access]

Repent · External Since: Nov 05, 2009 Posts: 5

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

On Thu, 05 Nov 2009 13:57:09 -0600, Marshall Barton
<marshbarton.RemoveThis@wowway.com> wrote:

>Repent wrote:
>>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.
>
>
>I think you spelled LF Run two different ways. Whichever
>one you are prompted to enter is spelled wrong (missing
>space?)

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

Repent wrote:
> . . . 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....

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

--
Marsh
MVP [MS Access]

Repent · External Since: Nov 05, 2009 Posts: 5

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?

On Thu, 05 Nov 2009 16:26:46 -0600, Marshall Barton
<marshbarton.DeleteThis@wowway.com> wrote:

>Repent wrote:
>> . . . 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....
>
>
>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??

Repent · External Since: Nov 05, 2009 Posts: 5

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

On Thu, 05 Nov 2009 15:45:55 -0800, Repent <repent34 RemoveThis @hotmail.com>
wrote:

>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?
>
>
>
>On Thu, 05 Nov 2009 16:26:46 -0600, Marshall Barton
><marshbarton RemoveThis @wowway.com> wrote:
>
>>Repent wrote:
>>> . . . 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....
>>
>>
>>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??

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

Repent wrote:

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

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?

--
Marsh
MVP [MS Access]

Repent · External Since: Nov 05, 2009 Posts: 5

it's all good now thank you very much for your help!

chris

On Fri, 06 Nov 2009 11:22:12 -0600, Marshall Barton
<marshbarton.TakeThisOut@wowway.com> wrote:

>Repent wrote:
>
>>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!!
>
>
>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?