|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 27th, 2008, 08:04 PM
posted to microsoft.public.access.queries
|
|||
|
|||
skip null value in field when calculating
I'm trying to calculate the score from survey questions, but don't get an
accurate total if there are null scores. I'm working with over 40,000 surveys so filling in the null fields to something isn't possible. Is there a formula I can add to my query that skips the null fields and totals the score(s) without it? Thanks 
|
|
#2
October 27th, 2008, 08:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
skip null value in field when calculating
Check out the Nz function.
-- KARL DEWEY Build a little - Test a little "Annemarie" wrote: I'm trying to calculate the score from survey questions, but don't get an accurate total if there are null scores. I'm working with over 40,000 surveys so filling in the null fields to something isn't possible. Is there a formula I can add to my query that skips the null fields and totals the score(s) without it? Thanks
|
|
#3
October 27th, 2008, 08:24 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
skip null value in field when calculating
This is what I have:
Expr1: Nz(([comments_tbl]![Question_1]+[comments_tbl]![Question_2]+[comments_tbl]![Question_3]+[comments_tbl]![Question_4]+[comments_tbl]![Question_5])/5) and it doesn't work. comments_tbl is the name of my table the query is pulling the data from Question_1 - Question_5 is the name of the fields in the table "KARL DEWEY" wrote: Check out the Nz function. -- KARL DEWEY Build a little - Test a little "Annemarie" wrote: I'm trying to calculate the score from survey questions, but don't get an accurate total if there are null scores. I'm working with over 40,000 surveys so filling in the null fields to something isn't possible. Is there a formula I can add to my query that skips the null fields and totals the score(s) without it? Thanks
|
|
#4
October 27th, 2008, 08:46 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
skip null value in field when calculating
Try this --
(Nz([comments_tbl].[Question_1],0)+Nz([comments_tbl].[Question_2],0)+Nz([comments_tbl].[Question_3],0)+Nz([comments_tbl]![Question_4],0)+Nz([comments_tbl].[Question_5],0))/5 -- KARL DEWEY Build a little - Test a little "Annemarie" wrote: This is what I have: Expr1: Nz(([comments_tbl]![Question_1]+[comments_tbl]![Question_2]+[comments_tbl]![Question_3]+[comments_tbl]![Question_4]+[comments_tbl]![Question_5])/5) and it doesn't work. comments_tbl is the name of my table the query is pulling the data from Question_1 - Question_5 is the name of the fields in the table "KARL DEWEY" wrote: Check out the Nz function. -- KARL DEWEY Build a little - Test a little "Annemarie" wrote: I'm trying to calculate the score from survey questions, but don't get an accurate total if there are null scores. I'm working with over 40,000 surveys so filling in the null fields to something isn't possible. Is there a formula I can add to my query that skips the null fields and totals the score(s) without it? Thanks
|
|
#5
October 27th, 2008, 08:57 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
skip null value in field when calculating
that worked, thank you!
"KARL DEWEY" wrote: Try this -- (Nz([comments_tbl].[Question_1],0)+Nz([comments_tbl].[Question_2],0)+Nz([comments_tbl].[Question_3],0)+Nz([comments_tbl]![Question_4],0)+Nz([comments_tbl].[Question_5],0))/5 -- KARL DEWEY Build a little - Test a little "Annemarie" wrote: This is what I have: Expr1: Nz(([comments_tbl]![Question_1]+[comments_tbl]![Question_2]+[comments_tbl]![Question_3]+[comments_tbl]![Question_4]+[comments_tbl]![Question_5])/5) and it doesn't work. comments_tbl is the name of my table the query is pulling the data from Question_1 - Question_5 is the name of the fields in the table "KARL DEWEY" wrote: Check out the Nz function. -- KARL DEWEY Build a little - Test a little "Annemarie" wrote: I'm trying to calculate the score from survey questions, but don't get an accurate total if there are null scores. I'm working with over 40,000 surveys so filling in the null fields to something isn't possible. Is there a formula I can add to my query that skips the null fields and totals the score(s) without it? Thanks
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
