Dividing one Control by another Control to find the Average.

[Guest]

Please help,

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.

 

[Marshall Barton]

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.

 

[Guest]

Marshall,

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.

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.

 

[Marshall Barton]

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]

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.

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.

 

[Guest]

Marshall,

You're right. My count is counting every record whether or not it is
Null. I have tried the two following formulas but both count all the records
no matter what.

=Count([Header Stitch #1])+Count([Header Stitch #2])+Count([Header Stitch
#3])+Count([Header Stitch #4])+Count([Header Stitch #5])

and

=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))

For one of my Types, one of the records has no entry in it, so there
should only be 69 Records counted but in both of the above cases, the formula
comes up with 70. These records are actually in Text Fromat. Would this make
the difference?

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]

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.

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.

 

[Marshall Barton]

The count you are trying to do here can give you just about
any result up to 5 times the number of records, because you
are adding 5 counts together.

Since you say these fields are Text fields, it's possible
that the fields do not contain Null if you have the fields'
AllowZeroLength property set to Yes. I have never seen a
reason to use that setting, but it is a default. So, if you
don't pay any attention to it, you won't know what's going
on. Note that if you have the Required property set to Yes,
you will not have Null in any record.

Did you think about the issue of normalization that I raised
earlier? Part of your problem may very well be caused by
having a table structure that is at cross purposes to
standard database operations. If you don't understand what
I'm going on about with this, take at look at:
http://office.microsoft.com/training/Training.aspx?AssetID=RP061494301033

At the risk of digging that hole even deeper, you might(??)
want to experiment with:

=Abs(Sum(Nz([Header Stitch #1], "") <> "")) +
Abs(Sum(Nz([Header Stitch #2], "") <> "")) +
Abs(Sum(Nz([Header Stitch #3], "") <> "")) +
Abs(Sum(Nz([Header Stitch #4], "") <> "")) +
Abs(Sum(Nz([Header Stitch #5], "") <> ""))
--
Marsh
MVP [MS Access]

You're right. My count is counting every record whether or not it is
Null. I have tried the two following formulas but both count all the records
no matter what.

=Count([Header Stitch #1])+Count([Header Stitch #2])+Count([Header Stitch
#3])+Count([Header Stitch #4])+Count([Header Stitch #5])

and

=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))

For one of my Types, one of the records has no entry in it, so there
should only be 69 Records counted but in both of the above cases, the formula
comes up with 70. These records are actually in Text Fromat. Would this make
the difference?

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.

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.

:

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.

 

[Guest]

Marshall,

Thanks guy! That did it. To find my average I used the following and it
worked great....

=(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]))))/(Abs(Sum(Nz([Header Stitch
#1],"")<>""))+Abs(Sum(Nz([Header Stitch #2],"")<>""))+Abs(Sum(Nz([Header
Stitch #3],"")<>""))+Abs(Sum(Nz([Header Stitch
#4],"")<>""))+Abs(Sum(Nz([Header Stitch #5],"")<>"")))

The count you are trying to do here can give you just about
any result up to 5 times the number of records, because you
are adding 5 counts together.

Since you say these fields are Text fields, it's possible
that the fields do not contain Null if you have the fields'
AllowZeroLength property set to Yes. I have never seen a
reason to use that setting, but it is a default. So, if you
don't pay any attention to it, you won't know what's going
on. Note that if you have the Required property set to Yes,
you will not have Null in any record.

Did you think about the issue of normalization that I raised
earlier? Part of your problem may very well be caused by
having a table structure that is at cross purposes to
standard database operations. If you don't understand what
I'm going on about with this, take at look at:
http://office.microsoft.com/training/Training.aspx?AssetID=RP061494301033

At the risk of digging that hole even deeper, you might(??)
want to experiment with:

=Abs(Sum(Nz([Header Stitch #1], "") <> "")) +
Abs(Sum(Nz([Header Stitch #2], "") <> "")) +
Abs(Sum(Nz([Header Stitch #3], "") <> "")) +
Abs(Sum(Nz([Header Stitch #4], "") <> "")) +
Abs(Sum(Nz([Header Stitch #5], "") <> ""))
--
Marsh
MVP [MS Access]

You're right. My count is counting every record whether or not it is
Null. I have tried the two following formulas but both count all the records
no matter what.

=Count([Header Stitch #1])+Count([Header Stitch #2])+Count([Header Stitch
#3])+Count([Header Stitch #4])+Count([Header Stitch #5])

and

=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))

For one of my Types, one of the records has no entry in it, so there
should only be 69 Records counted but in both of the above cases, the formula
comes up with 70. These records are actually in Text Fromat. Would this make
the difference?

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.


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.

:

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.

 

[Guest]

Marshall,

I must be going brain dead. I have another question about finding the
Min & Max values for each of my fields. Should I start another Thread or can
we just continue with this one?

Marshall,

Thanks guy! That did it. To find my average I used the following and it
worked great....

=(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]))))/(Abs(Sum(Nz([Header Stitch
#1],"")<>""))+Abs(Sum(Nz([Header Stitch #2],"")<>""))+Abs(Sum(Nz([Header
Stitch #3],"")<>""))+Abs(Sum(Nz([Header Stitch
#4],"")<>""))+Abs(Sum(Nz([Header Stitch #5],"")<>"")))

The count you are trying to do here can give you just about
any result up to 5 times the number of records, because you
are adding 5 counts together.

Since you say these fields are Text fields, it's possible
that the fields do not contain Null if you have the fields'
AllowZeroLength property set to Yes. I have never seen a
reason to use that setting, but it is a default. So, if you
don't pay any attention to it, you won't know what's going
on. Note that if you have the Required property set to Yes,
you will not have Null in any record.

Did you think about the issue of normalization that I raised
earlier? Part of your problem may very well be caused by
having a table structure that is at cross purposes to
standard database operations. If you don't understand what
I'm going on about with this, take at look at:
http://office.microsoft.com/training/Training.aspx?AssetID=RP061494301033

At the risk of digging that hole even deeper, you might(??)
want to experiment with:

=Abs(Sum(Nz([Header Stitch #1], "") <> "")) +
Abs(Sum(Nz([Header Stitch #2], "") <> "")) +
Abs(Sum(Nz([Header Stitch #3], "") <> "")) +
Abs(Sum(Nz([Header Stitch #4], "") <> "")) +
Abs(Sum(Nz([Header Stitch #5], "") <> ""))
--
Marsh
MVP [MS Access]

You're right. My count is counting every record whether or not it is
Null. I have tried the two following formulas but both count all the records
no matter what.

=Count([Header Stitch #1])+Count([Header Stitch #2])+Count([Header Stitch
#3])+Count([Header Stitch #4])+Count([Header Stitch #5])

and

=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))

For one of my Types, one of the records has no entry in it, so there
should only be 69 Records counted but in both of the above cases, the formula
comes up with 70. These records are actually in Text Fromat. Would this make
the difference?

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


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.

:

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.

 

[Marshall Barton]

Ask away, other than helping you dig that hole feeper and
deeper, I am happy to keep this thread going ;-)

Don't forget that Min and Max are also aggregate functions
that you use the same way you use Count and Sum.
--
Marsh
MVP [MS Access]

I must be going brain dead. I have another question about finding the
Min & Max values for each of my fields. Should I start another Thread or can
we just continue with this one?

Thanks guy! That did it. To find my average I used the following and it
worked great....

=(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]))))/(Abs(Sum(Nz([Header Stitch
#1],"")<>""))+Abs(Sum(Nz([Header Stitch #2],"")<>""))+Abs(Sum(Nz([Header
Stitch #3],"")<>""))+Abs(Sum(Nz([Header Stitch
#4],"")<>""))+Abs(Sum(Nz([Header Stitch #5],"")<>"")))

The count you are trying to do here can give you just about
any result up to 5 times the number of records, because you
are adding 5 counts together.

Since you say these fields are Text fields, it's possible
that the fields do not contain Null if you have the fields'
AllowZeroLength property set to Yes. I have never seen a
reason to use that setting, but it is a default. So, if you
don't pay any attention to it, you won't know what's going
on. Note that if you have the Required property set to Yes,
you will not have Null in any record.

Did you think about the issue of normalization that I raised
earlier? Part of your problem may very well be caused by
having a table structure that is at cross purposes to
standard database operations. If you don't understand what
I'm going on about with this, take at look at:
http://office.microsoft.com/training/Training.aspx?AssetID=RP061494301033

At the risk of digging that hole even deeper, you might(??)
want to experiment with:

=Abs(Sum(Nz([Header Stitch #1], "") <> "")) +
Abs(Sum(Nz([Header Stitch #2], "") <> "")) +
Abs(Sum(Nz([Header Stitch #3], "") <> "")) +
Abs(Sum(Nz([Header Stitch #4], "") <> "")) +
Abs(Sum(Nz([Header Stitch #5], "") <> ""))


Hoopster wrote:
You're right. My count is counting every record whether or not it is
Null. I have tried the two following formulas but both count all the records
no matter what.

=Count([Header Stitch #1])+Count([Header Stitch #2])+Count([Header Stitch
#3])+Count([Header Stitch #4])+Count([Header Stitch #5])

and

=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))

For one of my Types, one of the records has no entry in it, so there
should only be 69 Records counted but in both of the above cases, the formula
comes up with 70. These records are actually in Text Fromat. Would this make
the difference?

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


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.

:

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.

 

[Guest]

Well, I can't seam to find the right combination to extract the Minimum and
Maximum Values from Header Stitch #1, Header Stitch #2, Header Stitch #3,
Header Stitch #4 & Header Stitch #5. This has been driving me nuts. My other
question is this, would it have been better to do all these calulations in
Visual Basic Code instead of putting it in the Control Source?

Ask away, other than helping you dig that hole feeper and
deeper, I am happy to keep this thread going ;-)

Don't forget that Min and Max are also aggregate functions
that you use the same way you use Count and Sum.
--
Marsh
MVP [MS Access]

I must be going brain dead. I have another question about finding the
Min & Max values for each of my fields. Should I start another Thread or can
we just continue with this one?

Thanks guy! That did it. To find my average I used the following and it
worked great....

=(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]))))/(Abs(Sum(Nz([Header Stitch
#1],"")<>""))+Abs(Sum(Nz([Header Stitch #2],"")<>""))+Abs(Sum(Nz([Header
Stitch #3],"")<>""))+Abs(Sum(Nz([Header Stitch
#4],"")<>""))+Abs(Sum(Nz([Header Stitch #5],"")<>"")))

:
The count you are trying to do here can give you just about
any result up to 5 times the number of records, because you
are adding 5 counts together.

Since you say these fields are Text fields, it's possible
that the fields do not contain Null if you have the fields'
AllowZeroLength property set to Yes. I have never seen a
reason to use that setting, but it is a default. So, if you
don't pay any attention to it, you won't know what's going
on. Note that if you have the Required property set to Yes,
you will not have Null in any record.

Did you think about the issue of normalization that I raised
earlier? Part of your problem may very well be caused by
having a table structure that is at cross purposes to
standard database operations. If you don't understand what
I'm going on about with this, take at look at:
http://office.microsoft.com/training/Training.aspx?AssetID=RP061494301033

At the risk of digging that hole even deeper, you might(??)
want to experiment with:

=Abs(Sum(Nz([Header Stitch #1], "") <> "")) +
Abs(Sum(Nz([Header Stitch #2], "") <> "")) +
Abs(Sum(Nz([Header Stitch #3], "") <> "")) +
Abs(Sum(Nz([Header Stitch #4], "") <> "")) +
Abs(Sum(Nz([Header Stitch #5], "") <> ""))


Hoopster wrote:
You're right. My count is counting every record whether or not it is
Null. I have tried the two following formulas but both count all the records
no matter what.

=Count([Header Stitch #1])+Count([Header Stitch #2])+Count([Header Stitch
#3])+Count([Header Stitch #4])+Count([Header Stitch #5])

and

=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))

For one of my Types, one of the records has no entry in it, so there
should only be 69 Records counted but in both of the above cases, the formula
comes up with 70. These records are actually in Text Fromat. Would this make
the difference?

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


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.

:

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.

 

[Marshall Barton]

No. Using VBA is not a better way to do this. The best way
is to use a query on normalized tables with the right
property settings.

Try this kind of thing for the Min and Max:
=Max(IIf([Header Stitch #1] = "", Null, [Header Stitch #1]))

If you acsolutely can not mormalize your tables, the least
you should do is set the Allow Zero Length property to No so
you can eliminate all these goofy checks for =""
--
Marsh
MVP [MS Access]

Well, I can't seam to find the right combination to extract the Minimum and
Maximum Values from Header Stitch #1, Header Stitch #2, Header Stitch #3,
Header Stitch #4 & Header Stitch #5. This has been driving me nuts. My other
question is this, would it have been better to do all these calulations in
Visual Basic Code instead of putting it in the Control Source?

Ask away, other than helping you dig that hole feeper and
deeper, I am happy to keep this thread going ;-)

Don't forget that Min and Max are also aggregate functions
that you use the same way you use Count and Sum.

I must be going brain dead. I have another question about finding the
Min & Max values for each of my fields. Should I start another Thread or can
we just continue with this one?

:
Thanks guy! That did it. To find my average I used the following and it
worked great....

=(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]))))/(Abs(Sum(Nz([Header Stitch
#1],"")<>""))+Abs(Sum(Nz([Header Stitch #2],"")<>""))+Abs(Sum(Nz([Header
Stitch #3],"")<>""))+Abs(Sum(Nz([Header Stitch
#4],"")<>""))+Abs(Sum(Nz([Header Stitch #5],"")<>"")))

:
The count you are trying to do here can give you just about
any result up to 5 times the number of records, because you
are adding 5 counts together.

Since you say these fields are Text fields, it's possible
that the fields do not contain Null if you have the fields'
AllowZeroLength property set to Yes. I have never seen a
reason to use that setting, but it is a default. So, if you
don't pay any attention to it, you won't know what's going
on. Note that if you have the Required property set to Yes,
you will not have Null in any record.

Did you think about the issue of normalization that I raised
earlier? Part of your problem may very well be caused by
having a table structure that is at cross purposes to
standard database operations. If you don't understand what
I'm going on about with this, take at look at:
http://office.microsoft.com/training/Training.aspx?AssetID=RP061494301033

At the risk of digging that hole even deeper, you might(??)
want to experiment with:

=Abs(Sum(Nz([Header Stitch #1], "") <> "")) +
Abs(Sum(Nz([Header Stitch #2], "") <> "")) +
Abs(Sum(Nz([Header Stitch #3], "") <> "")) +
Abs(Sum(Nz([Header Stitch #4], "") <> "")) +
Abs(Sum(Nz([Header Stitch #5], "") <> ""))


Hoopster wrote:
You're right. My count is counting every record whether or not it is
Null. I have tried the two following formulas but both count all the records
no matter what.

=Count([Header Stitch #1])+Count([Header Stitch #2])+Count([Header Stitch
#3])+Count([Header Stitch #4])+Count([Header Stitch #5])

and

=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))

For one of my Types, one of the records has no entry in it, so there
should only be 69 Records counted but in both of the above cases, the formula
comes up with 70. These records are actually in Text Fromat. Would this make
the difference?

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


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.

:

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.

 

[Guest]

Marshall,

This works great to get the Min and Max Values for Header Stitch #1 and
I can get the Average by using =(Sum(Val(nz([Header Stitch
#1]))))/(Abs(Sum(Nz([Header Stitch #1],"")<>""))). The Report I am trying to
create will be a summery of all the Fields in my Database. Therefore what I
am really trying to do is get the Minimum Value of all 5 Header Stitch
Fields. If the Minimum Value for Header Stitch #1 is 6.000 but Header Stitch
#3 has a Minimum Value of 5.500, then 5.500 is the Number I want to show as
the Minimum Value. Also the Average for all. These Numbers will then be used
later to create Charts and Graphs.

No. Using VBA is not a better way to do this. The best way
is to use a query on normalized tables with the right
property settings.

Try this kind of thing for the Min and Max:
=Max(IIf([Header Stitch #1] = "", Null, [Header Stitch #1]))

If you acsolutely can not mormalize your tables, the least
you should do is set the Allow Zero Length property to No so
you can eliminate all these goofy checks for =""
--
Marsh
MVP [MS Access]

Well, I can't seam to find the right combination to extract the Minimum and
Maximum Values from Header Stitch #1, Header Stitch #2, Header Stitch #3,
Header Stitch #4 & Header Stitch #5. This has been driving me nuts. My other
question is this, would it have been better to do all these calulations in
Visual Basic Code instead of putting it in the Control Source?

Ask away, other than helping you dig that hole feeper and
deeper, I am happy to keep this thread going ;-)

Don't forget that Min and Max are also aggregate functions
that you use the same way you use Count and Sum.


Hoopster wrote:
I must be going brain dead. I have another question about finding the
Min & Max values for each of my fields. Should I start another Thread or can
we just continue with this one?

:
Thanks guy! That did it. To find my average I used the following and it
worked great....

=(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]))))/(Abs(Sum(Nz([Header Stitch
#1],"")<>""))+Abs(Sum(Nz([Header Stitch #2],"")<>""))+Abs(Sum(Nz([Header
Stitch #3],"")<>""))+Abs(Sum(Nz([Header Stitch
#4],"")<>""))+Abs(Sum(Nz([Header Stitch #5],"")<>"")))

:
The count you are trying to do here can give you just about
any result up to 5 times the number of records, because you
are adding 5 counts together.

Since you say these fields are Text fields, it's possible
that the fields do not contain Null if you have the fields'
AllowZeroLength property set to Yes. I have never seen a
reason to use that setting, but it is a default. So, if you
don't pay any attention to it, you won't know what's going
on. Note that if you have the Required property set to Yes,
you will not have Null in any record.

Did you think about the issue of normalization that I raised
earlier? Part of your problem may very well be caused by
having a table structure that is at cross purposes to
standard database operations. If you don't understand what
I'm going on about with this, take at look at:
http://office.microsoft.com/training/Training.aspx?AssetID=RP061494301033

At the risk of digging that hole even deeper, you might(??)
want to experiment with:

=Abs(Sum(Nz([Header Stitch #1], "") <> "")) +
Abs(Sum(Nz([Header Stitch #2], "") <> "")) +
Abs(Sum(Nz([Header Stitch #3], "") <> "")) +
Abs(Sum(Nz([Header Stitch #4], "") <> "")) +
Abs(Sum(Nz([Header Stitch #5], "") <> ""))


Hoopster wrote:
You're right. My count is counting every record whether or not it is
Null. I have tried the two following formulas but both count all the records
no matter what.

=Count([Header Stitch #1])+Count([Header Stitch #2])+Count([Header Stitch
#3])+Count([Header Stitch #4])+Count([Header Stitch #5])

and

=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))

For one of my Types, one of the records has no entry in it, so there
should only be 69 Records counted but in both of the above cases, the formula
comes up with 70. These records are actually in Text Fromat. Would this make
the difference?

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


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.

:

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.

 

[Marshall Barton]

I'm afraid I can't think of a reasonable reply to this
latest twist. The lack of a normalized table design is the
root cause of all this confusion and, since Access is a
relational database system, it is not designed to deal with
a spreadsheet data arrangement. To further exacerbate the
issue, the number of people with experience on problems like
this are (hopefully) few and far between, so the odds of
your getting a nice clear answer is not very good.

I suppose that you could use a messy nested IIf construct
(or create your own function) to determine which field has
the minimum value. If you are unable to change your table
structure, another possibility is to create a UNION query
(or series of queries) that reorganizes you data in a
normalized, rational way.
--
Marsh
MVP [MS Access]

This works great to get the Min and Max Values for Header Stitch #1 and
I can get the Average by using =(Sum(Val(nz([Header Stitch
#1]))))/(Abs(Sum(Nz([Header Stitch #1],"")<>""))). The Report I am trying to
create will be a summery of all the Fields in my Database. Therefore what I
am really trying to do is get the Minimum Value of all 5 Header Stitch
Fields. If the Minimum Value for Header Stitch #1 is 6.000 but Header Stitch
#3 has a Minimum Value of 5.500, then 5.500 is the Number I want to show as
the Minimum Value. Also the Average for all. These Numbers will then be used
later to create Charts and Graphs.

No. Using VBA is not a better way to do this. The best way
is to use a query on normalized tables with the right
property settings.

Try this kind of thing for the Min and Max:
=Max(IIf([Header Stitch #1] = "", Null, [Header Stitch #1]))

If you acsolutely can not mormalize your tables, the least
you should do is set the Allow Zero Length property to No so
you can eliminate all these goofy checks for =""

Well, I can't seam to find the right combination to extract the Minimum and
Maximum Values from Header Stitch #1, Header Stitch #2, Header Stitch #3,
Header Stitch #4 & Header Stitch #5. This has been driving me nuts. My other
question is this, would it have been better to do all these calulations in
Visual Basic Code instead of putting it in the Control Source?

:
Ask away, other than helping you dig that hole feeper and
deeper, I am happy to keep this thread going ;-)

Don't forget that Min and Max are also aggregate functions
that you use the same way you use Count and Sum.


Hoopster wrote:
I must be going brain dead. I have another question about finding the
Min & Max values for each of my fields. Should I start another Thread or can
we just continue with this one?

:
Thanks guy! That did it. To find my average I used the following and it
worked great....

=(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]))))/(Abs(Sum(Nz([Header Stitch
#1],"")<>""))+Abs(Sum(Nz([Header Stitch #2],"")<>""))+Abs(Sum(Nz([Header
Stitch #3],"")<>""))+Abs(Sum(Nz([Header Stitch
#4],"")<>""))+Abs(Sum(Nz([Header Stitch #5],"")<>"")))

:
The count you are trying to do here can give you just about
any result up to 5 times the number of records, because you
are adding 5 counts together.

Since you say these fields are Text fields, it's possible
that the fields do not contain Null if you have the fields'
AllowZeroLength property set to Yes. I have never seen a
reason to use that setting, but it is a default. So, if you
don't pay any attention to it, you won't know what's going
on. Note that if you have the Required property set to Yes,
you will not have Null in any record.

Did you think about the issue of normalization that I raised
earlier? Part of your problem may very well be caused by
having a table structure that is at cross purposes to
standard database operations. If you don't understand what
I'm going on about with this, take at look at:
http://office.microsoft.com/training/Training.aspx?AssetID=RP061494301033

At the risk of digging that hole even deeper, you might(??)
want to experiment with:

=Abs(Sum(Nz([Header Stitch #1], "") <> "")) +
Abs(Sum(Nz([Header Stitch #2], "") <> "")) +
Abs(Sum(Nz([Header Stitch #3], "") <> "")) +
Abs(Sum(Nz([Header Stitch #4], "") <> "")) +
Abs(Sum(Nz([Header Stitch #5], "") <> ""))


Hoopster wrote:
You're right. My count is counting every record whether or not it is
Null. I have tried the two following formulas but both count all the records
no matter what.

=Count([Header Stitch #1])+Count([Header Stitch #2])+Count([Header Stitch
#3])+Count([Header Stitch #4])+Count([Header Stitch #5])

and

=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))

For one of my Types, one of the records has no entry in it, so there
should only be 69 Records counted but in both of the above cases, the formula
comes up with 70. These records are actually in Text Fromat. Would this make
the difference?

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


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.

:

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.