Average value of a string expression displayed in a report - 2 par

[Guest]

1. I want to average a field with a string expression (equals either N or
E). If I just use the AVG function on the field in the report it returns
either N or E not a percentage. How can I get the percentage?

2. I used the AVG function to evaluate the average of a Yes / No field on my
report - the calculation is correct but is dispalyed as a negative
number??????

Any help would be appreicated.

Thanks.

 

[Tim Ferguson]

"=?Utf-8?B?QyBNYXJrb3dpdHo=?=" <[email protected]>
wrote in
I am afraid some basic reading around data types is required.

1. I want to average a field with a string expression (equals either
N or E). If I just use the AVG function on the field in the report it
returns either N or E not a percentage. How can I get the percentage?

Well, you cannot really add an N to an E in any meaningful sense. If you
want to know the number of Ns as a fraction of all the Ns and Es, then you
have to do that calculation:

dblMyFraction = DCount("*", "MyTable", "Somefield = ""N"") / _
DCount("*", "MyTable", "Somefield = ""N"" OR SomeField = ""E"")


There is probably a fancy way to do it with an IIF

SELECT SUM(IIF(Somefield="N",1,0)) / SUM(IIF(SomeField LIKE "[NE]",1,0))
FROM MyTable
etc

2. I used the AVG function to evaluate the average of a Yes / No field
on my report - the calculation is correct but is dispalyed as a
negative number??????

This relies on the coincidence that TRUE is stored as -1, and FALSE is 0.
Access forgivingly allows you to add a TRUE to a TRUE and get 2. Hmmmm. You
_can_ continue to use this, and you will _probably_ be alright. Bear in
mind, though, that if the dawn of 64-bit processors means that TRUE takes
on a value of +32767 instead, your code will be, as we say in the trade,
stuffed.

In my book, it's safer to do it properly first time round....


Best wishes


Tim F

 

[John Vinson]

1. I want to average a field with a string expression (equals either N or
E). If I just use the AVG function on the field in the report it returns
either N or E not a percentage. How can I get the percentage?

2. I used the AVG function to evaluate the average of a Yes / No field on my
report - the calculation is correct but is dispalyed as a negative
number??????

The AVG function has a specific definition: it adds the numeric values
in the field specified, and divides that numeric sum by the number of
non-NULL values. What is the numeric value of "N" + "N" + "E"? What do
you get when you divide that number by 3?

A Yes/No field is stored as the number -1 for True, and 0 for False.
Avg() will do exactly the same thing; if there are ten records, of
which six are True, it will add six -1's and four zeros, get -6 as the
result, and divide by 10: the average will be -0.6.

In other words - AVG is not the correct function for what you're
trying to accomplish. I'm not sure what you *are* trying to
accomplish, so I can't suggest what you should use. Could you explain?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

I am trying to get values based on sales but the date fields are text and
yes/no fields. So I guess what I want to accomplish is to:

1. Count the number of N and E and get an average of the count.

2. The Yes / No field is the same way - I want to count them and then get an
average displayed as a percentage.

I hope my explanation is more clear now on what I want to accomplish.

Thanks to both of you for your input.

 

[John Vinson]

I am trying to get values based on sales but the date fields are text and
yes/no fields. So I guess what I want to accomplish is to:

1. Count the number of N and E and get an average of the count.

I'm still confused. The "average of the count"? If you have 400
records, 300 N and 100 E, what "average" do you want to see? Two
percentages, 75% N and 25% E? 200 (the average of 300 and 100)? or
what?

It seems you're using the word "average" in a different way than I
understand the term!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John,

Yes, I want the percent of N - in the overall sales (75% as in your
example). I am sorry I wasn't clear.

 

[John Vinson]

I am trying to get values based on sales but the date fields are text and
yes/no fields. So I guess what I want to accomplish is to:

1. Count the number of N and E and get an average of the count.

To get the fraction of total records which contain N use a Sum
(instead of a Count). Put an expression in the totals query:

Sum(IIF([Fieldname] = "N", 1, 0)) / Count(*)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps