Hello,
Need help on calculation on the average with null value. Let's say that the table has four fields, all numeric.
Field Names are: MonthA, MonthB, MonthC and MonthD. Let's say the data are; $400, (null), $500, $600.
How can I use a query wizard to construct a query that will return me an average of $500, instead of a blank? I created a calculate field called, "First4months".
Someone told me that Access won't accept the null value in any field, if there is a calculation involved.
Is there a way for me to run an average for these four months in Access without exporting to Excel and importing back?
Second question: Let's say, I have another four months, MonthE, MonthF, MonthG and MonthH. Data are: $500, $600, null, and $100. I name the calculated fields, "Middle4months." As you see, the average will be $400.
Is there anyway I can run the average for the entire eight months or I need to sum the "First4months" and "Middle4Months" to find an average?
I keep getting error about cannot use aggregate in another aggregate.
Do I need to construct two queries on the top of each other? If I do, am I required to use all the fields or just selected fields? Please help. Thank you again.
Anyone has the ideas, please let me know.
Need help on calculation on the average with null value. Let's say that the table has four fields, all numeric.
Field Names are: MonthA, MonthB, MonthC and MonthD. Let's say the data are; $400, (null), $500, $600.
How can I use a query wizard to construct a query that will return me an average of $500, instead of a blank? I created a calculate field called, "First4months".
Someone told me that Access won't accept the null value in any field, if there is a calculation involved.
Is there a way for me to run an average for these four months in Access without exporting to Excel and importing back?
Second question: Let's say, I have another four months, MonthE, MonthF, MonthG and MonthH. Data are: $500, $600, null, and $100. I name the calculated fields, "Middle4months." As you see, the average will be $400.
Is there anyway I can run the average for the entire eight months or I need to sum the "First4months" and "Middle4Months" to find an average?
I keep getting error about cannot use aggregate in another aggregate.
Do I need to construct two queries on the top of each other? If I do, am I required to use all the fields or just selected fields? Please help. Thank you again.
Anyone has the ideas, please let me know.