Help!

[k12345]

I am not very skilled in Access and need to create a very basic form for
quality assurance. I have the form set up with a set amount of questions and
a score box next to each one. I have built an expression in the final box
where I am able to add all of them and divide by the amount of standards to
come up with the average. This is the problem: I need to be able to also
include "N/A" as an answer and make sure that the question that has this will
not be included in the final average. This could be different every time
depending on the case. Some may have 1 or another may have 5 and need to
make sure that the expression will change the number I need to average with...
.. Am I making any sense?

Any help would be appreciated.

Thank you.

 

[Guest]

k12345,

What you want can be done, however, I have a few questions.

What type of field is the "score box" for each queston?

If this field is a numeric field, then you will need to determine just how
you are going to flag the field to indicate the "N/A" value. The fact that
the field is numeric will prevent you from just entering "N/A" in the field.

The general concept would be that you would have some method of specifying
(flagging) each question where the "N/A" applies. Each time you flag a
question you would increment a variable by 1. If you then remove the flag on
a question you would simply decrement that variable by 1. Then at the time
you want to calculate the average you would simply substract the variable
that is holding the number of "N/A" questions from the total possible
questions and then use that number to obtain the average.

 

[k12345 via AccessMonster.com]

I initially had the "score box" as a numeric field, but changed it to a text
box so I may include "N/A" as an option. Is it possible to have an
expression where if it recognized that there was nothing in the box, it would
not include that box in the averaging?

k12345,

What you want can be done, however, I have a few questions.

What type of field is the "score box" for each queston?

If this field is a numeric field, then you will need to determine just how
you are going to flag the field to indicate the "N/A" value. The fact that
the field is numeric will prevent you from just entering "N/A" in the field.

The general concept would be that you would have some method of specifying
(flagging) each question where the "N/A" applies. Each time you flag a
question you would increment a variable by 1. If you then remove the flag on
a question you would simply decrement that variable by 1. Then at the time
you want to calculate the average you would simply substract the variable
that is holding the number of "N/A" questions from the total possible
questions and then use that number to obtain the average.

I am not very skilled in Access and need to create a very basic form for
quality assurance. I have the form set up with a set amount of questions and

[quoted text clipped - 10 lines]

Thank you.

 

[Guest]

Yes, it would be possible to only have those questions where a value is
provided for the score.

You can use the variable that I described in my previous post, but use it in
a littel different way.

When the form opens, initilize the variable to zero. if you want to do it
this way, you would most likely want to make the field to be a numeric value
with a default value of zero.

Then you would check the value of each "score" text box and if the value is

0 then increment the variable by one. If it is zero (if the user leaves the

field blank or empty, you may want to replace the zero value) decrement the
variable value.

Then just use the variable value for averaging.

--
HTH

Mr B

I initially had the "score box" as a numeric field, but changed it to a text
box so I may include "N/A" as an option. Is it possible to have an
expression where if it recognized that there was nothing in the box, it would
not include that box in the averaging?

k12345,

What you want can be done, however, I have a few questions.

What type of field is the "score box" for each queston?

If this field is a numeric field, then you will need to determine just how
you are going to flag the field to indicate the "N/A" value. The fact that
the field is numeric will prevent you from just entering "N/A" in the field.

The general concept would be that you would have some method of specifying
(flagging) each question where the "N/A" applies. Each time you flag a
question you would increment a variable by 1. If you then remove the flag on
a question you would simply decrement that variable by 1. Then at the time
you want to calculate the average you would simply substract the variable
that is holding the number of "N/A" questions from the total possible
questions and then use that number to obtain the average.

I am not very skilled in Access and need to create a very basic form for
quality assurance. I have the form set up with a set amount of questions and

[quoted text clipped - 10 lines]

Thank you.

 

[John W. Vinson]

I initially had the "score box" as a numeric field, but changed it to a text
box so I may include "N/A" as an option. Is it possible to have an
expression where if it recognized that there was nothing in the box, it would
not include that box in the averaging?

I'd really suggest changing it back to a Number field, and leaving it
NULL for the n/a. NULL values will not be included in averaging (i.e.
the Avg() of 5, NULL, 6, NULL, NULL, and 7 is in fact 6).

You can set the Format property of the textbox in which this field is
displayed to

#;-#;#;"N/A"

to show it as a positive, negative, or zero number, or N/A for the
null value.

John W. Vinson [MVP]

 

[Guest]

John,

Great approach to this problem. As usual you have demonstrated why you have
received your MVP designation.

I have been at this stuff for a long time and did not realize two things
that you addressed in your answer. First the formatting of the text box to
display the value, including the "N/A". I must admit that many times I
forget about the formatting capabilities and what they can do. However, the
one thing that I was not aware of is that the Average function would not use
null values as part of the average. Just did not know it worked that way.

I just wanted to thank you for you continued participation in these
newsgroups. I have learned much from your postings as well as the posting of
many ohters.

Thanks again.

Mr B

 

[k12345 via AccessMonster.com]

Thank you so much for your response. I have done as instructed, but I have
one more question. Would you be able to advise exactly how I should have the
expression? I currently (and most likely, incorrectly) have all of the boxes
added and then divided by 24. I now know this must change so it recognizes
the null value. I did not use the average function since I did not know how
to use it.

Thank you.

John,

Great approach to this problem. As usual you have demonstrated why you have
received your MVP designation.

I have been at this stuff for a long time and did not realize two things
that you addressed in your answer. First the formatting of the text box to
display the value, including the "N/A". I must admit that many times I
forget about the formatting capabilities and what they can do. However, the
one thing that I was not aware of is that the Average function would not use
null values as part of the average. Just did not know it worked that way.

I just wanted to thank you for you continued participation in these
newsgroups. I have learned much from your postings as well as the posting of
many ohters.

Thanks again.

Mr B
[quoted text clipped - 14 lines]

John W. Vinson [MVP]

 

[Guest]

I would not want to speak for John, but below is an sql statement that uses
the Avg function.

SELECT Avg(tblInvNumsTemp.InvAmt) AS AvgOfInvAmt
FROM tblInvNumsTemp;

This sql statement is just a totals type query.

I think, what John was indicating was that if you have values in some
records and null in some records, using the Avg function will produce the
average of the records with existing values that are not null and will
average these values without using the null records at all.

I tested this before pasting the sql statement so I could see that it
produced what was needed and sure enough, it does. (Not that I questioned
John, but just wanted to test it out.) I have a table named
"tblInvNumsTemp". It has only two fields for testing: "InvNum" and "InvAmt".
I have a total of 9 records in the table, but only 2 of those records have a
value in the "InvAmt" field. The two values are $100 and $500. When the sql
statement above is run, the average is $250 even though there are 9 records
and 7 of them have a null value.

If you are not going to use the Avg function, then you would need to track
the number of instances where the user has entered a number in a field and
then use that number instead of the 24 that you indicated you are using.
This would go back to what I posted to start with.

As is often the case in Access, there are multiple ways to approach solving
any problem. It just depends of how you want to go about it.

--
HTH

Mr B

Thank you so much for your response. I have done as instructed, but I have
one more question. Would you be able to advise exactly how I should have the
expression? I currently (and most likely, incorrectly) have all of the boxes
added and then divided by 24. I now know this must change so it recognizes
the null value. I did not use the average function since I did not know how
to use it.

Thank you.

John,

Great approach to this problem. As usual you have demonstrated why you have
received your MVP designation.

I have been at this stuff for a long time and did not realize two things
that you addressed in your answer. First the formatting of the text box to
display the value, including the "N/A". I must admit that many times I
forget about the formatting capabilities and what they can do. However, the
one thing that I was not aware of is that the Average function would not use
null values as part of the average. Just did not know it worked that way.

I just wanted to thank you for you continued participation in these
newsgroups. I have learned much from your postings as well as the posting of
many ohters.

Thanks again.

Mr B

I initially had the "score box" as a numeric field, but changed it to a text
box so I may include "N/A" as an option. Is it possible to have an

[quoted text clipped - 14 lines]

John W. Vinson [MVP]