Re: Dividing one Control by another Control to find the Average.
- From: Marshall Barton <marshbarton@xxxxxxxxxx>
- Date: Fri, 22 Sep 2006 13:29:15 -0500
The core point I was trying to make was the bit about Null
values being ignored. Your Count expression is counting
every record whether the field contains Null or not. Try my
expression for counting.
In addition your statement of "without much luck" doesnt'
really tell me what problem you are talking about so you may
be struggling with additional issues that are not apparent
in your post.
It may (or may not) be important to avoid using the Val
function because it can do things you might expect. If the
stitch values are integer or long type, then I suggest that
you use the Int function instead:
=Sum(Int([Header Stitch #1])) + Sum(Int([Header Stitch #2]))
+ Sum(Int([Header Stitch #3])) + Sum(Int([Header Stitch
#4])) + SumInt(([Header Stitch #5]))
Note that all these calculation need to be done in the same
footer section.
If all of that does not take care of it, then post back with
more details about the result you are seeing.
--
Marsh
MVP [MS Access]
Hoopster wrote:
The Data in my Table is Text. That's the reason I used the Val Funtion.
I am getting the correct numbers for my Sum and Count. My delima is when
trying to divide the Sum Value by the Count Value. I am trying to do the
division in the Style Footer.
"Marshall Barton" wrote:
Hoopster wrote:
I am trying to divide one Control, call it Text 1 by another Control
Text 2 to find the average of several entries in a Access Report. I am
placing the Controls in the the Style Footer where I am adding the Values of
Control One and Counting the Number of entries in Control 2.
Control 1 - =Sum(Val(nz([Header Stitch #1])))+Sum(Val(nz([Header Stitch
#2])))+Sum(Val(nz([Header Stitch #3])))+Sum(Val(nz([Header Stitch
#4])))+Sum(Val(nz([Header Stitch #5])))
Control 2 - =Count(IIf([Header Stitch #1] Is Not
Null,1,0))+Count(IIf([Header Stitch #2] Is Not Null,1,0))+Count(IIf([Header
Stitch #3] Is Not Null,1,0))+Count(IIf([Header Stitch #4] Is Not
Null,1,0))+Count(IIf([Header Stitch #5] Is Not Null,1,0))
I am trying to Divide Control 1 by Control 2 using =(Val([Text 1])/Val([Text
2])) without much luck.
Note that the aggregate functions (Count, Sum, etc) ignore
Null values so there is no need to check for Null or use the
Nz function. Assuming your data is appropriate and the
field names are correct, I think all you need is:
=Sum([Header Stitch #1]) + Sum([Header Stitch #2]) +
Sum([Header Stitch #3]) + Sum([Header Stitch #4]) +
Sum([Header Stitch #5])
=Count([Header Stitch #1]) + Count([Header Stitch #2]) +
Count([Header Stitch #3]) + Count([Header Stitch #4]) +
Count([Header Stitch #5])
Note that unless all those fields are Text type fields,
there is no need to use the Val function so I dropped that
too.
***Warning*** The use of field (not control) names such as
Stitch1, Stich2, ... is a red flag that the table structure
is not normalized and will cause no end of trouble.
--
Marsh
MVP [MS Access]
.
- Follow-Ups:
- References:
- Re: Dividing one Control by another Control to find the Average.
- From: Marshall Barton
- Re: Dividing one Control by another Control to find the Average.
- Prev by Date: Re: Report based on 2 queries?
- Next by Date: Re: Duane Hookum
- Previous by thread: Re: Dividing one Control by another Control to find the Average.
- Next by thread: Re: Dividing one Control by another Control to find the Average.
- Index(es):
Relevant Pages
|
