doing math on summed totals
Repent
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 <spe...@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 <spe...@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
>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?
>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
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....
Marshall Barton
> . . . my control source for the text box is:
> =Sum([Waste%])/Sum([LF RUN])
>
>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
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
"*", 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 <repe...@hotmail.com>
wrote:
Marshall Barton
>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?
Repent
chris