Calculated total in PivotTable

Question from an Access newbie:
How are calculated totals used in queries and PivotTables? For example, how is the correct standard deviation determined in a PivotTalbe where many cells are empty? Say a PivotTable’s field has 10 rows but data occur in only 3 of the 10 rows; say the data are 5, 5 and 5. Using the built in AutoCalc function (StDev) will give the (wrong) answer 0 because only the 3 entries have been used rather than all 10 (the other 7, which I guess are absent or Null need to be taken into account and treated as zero). Any help with this would be fantastic.
Cheers

How are you determing that the standard deviation is erroneous? Have you applied the formula to your data in question?

The SD is correct given the way that Access calculates it; i.e. it has based the calculation on n=3, rather than n=10. In the PivotTable, there are many blank cells, because for any one field there isn't necessarily a value in all rows. However, to calculate the required SD, I need Access to treat the blanks in the table as zeros. So, in the example, if the field has three cells with the value '5' and seven cells with nothing (while another field may have 8 values and 2 blanks, and so on), I want Access to recognise that n=10 and not 3, treating the empty cells as zeros. This would produce the correct SD (2.4) and not the wrong SD of zero. I could do these calculations elsewhere, but I have several very large datasets (2-3 million points) and am trying to make the transition to Access, rather than keeping data in many text files that have a lot of redundant data.

Have you attempted to create default values of zero for these columns? If so, to what avail?

No. I don't know how. But the problem seems so simple. To give a real example: in one subset of data, there are 50 replicates and about 200 variables. But, because not all variables occur in every replicate, the replicate (row) by variable (column) table (in Pivot view) will of course only have values in cells where the variable does occur in a replicate. But in calculating the SD or mean, I want Access to use the total number of replicates for n (in this case 50), not just the number of replicates where the variable occurs, in which case the mean, for example, would be based on an n that could vary from 1 to 50.

Is the issue with the data being returned from the query/database or with the lack of data being inserted into the database?

Neither. Both the query runs as it should and the database is fine. The issue is with creating a calculated total that produces the correct means and standard deviations for each variable.

If the query is running fine, then if ten rows are chosing and seven are null, those should be defaulted to zero. Thus there would still be ten items in the resultset. Ten items would be passed to the standard deviation formula and n should equal ten.

isnull( yourColumn, 0 ). Or it may be ifnull, can't remember in access.

The query just counts the number of occurences for the variables and the PivotView shows counts by field for each replicate. In using Create Calculated Total only those cells with values are used in the calculation, which is not what I want. So how does one ensure that the empty cells default to zero? It looks like this could be a VBA problem or maybe a more involved SQL statement than can be produced using query Design View. Frustrating, because I was hoping Access would make life a little easier.