NULL Values in Total Queries (Access 2003) Bug or Deficiency

[Jinx]

Hi All

I am working on a small table of only 15 columns as below:
Project Name
Individual Staff Name
Year of Activity
Then 12 Columns for each month having time spent by each individual during that month on a given project.

I wanted to create a Total Query to come up with a Yearly total of time spent by each individual on a given project. The process is very simple just create a Total Query, use an aggregate function of SUM() and get the results. But not for me. What I have observed (just last night, and I don't know if any of you guys have already encountered this problem) is that you have to have either ZERO or a non-NULL value in each row of the coumns you are processing. Otherwise you get nothing in the Expression column of the Query.

I tried this with some values in the cells and the results were there but when I removed the data leaving only NULL Value in the cell, the resulting expression was empty.

Here is the query:
SELECT Table1.PROJECT, Table1.STAFF, Table1.W_YEAR, Sum(Table1.M_01) AS SumOfM_01, Sum(Table1.M_02) AS SumOfM_02, Sum(Table1.M_03) AS SumOfM_03, Sum(Table1.M_04) AS SumOfM_04, Sum(Table1.M_05) AS SumOfM_05, Sum(Table1.M_06) AS SumOfM_06, Sum(Table1.M_07) AS SumOfM_07, Sum(Table1.M_08) AS SumOfM_08, Sum(Table1.M_09) AS SumOfM_09, Sum(Table1.M_10) AS SumOfM_10, Sum(Table1.M_11) AS SumOfM_11, Sum(Table1.M_12) AS SumOfM_12, Sum([TABLE1.M_01]+[TABLE1.M_02]+[TABLE1.M_03]+[TABLE1.M_04]+[TABLE1.M_05]+[TABLE1.M_06]+[TABLE1.M_07]+[TABLE1.M_08]+[TABLE1.M_09]+[TABLE1.M_10]+[TABLE1.M_11]+[TABLE1.M_12]) AS Expr1
FROM Table1
GROUP BY Table1.PROJECT, Table1.STAFF, Table1.W_YEAR;

Surprised, as in real life this is not always the scenario. You may ecounter an individual who joined your project team in september only having NO hours during the first 8 months. Yes you can put zero here but in most of the cases there is a whole lot diffrence between a ZERO value and a NULL value. Think of Blood Counts in a patient whose data is not yet available. Putting ZERO here makes a lot of difference. You know what I mean. Anyway, I am posting this for the reference and your comments.

Thanks and have a good day!!!!

Jinx

[Tanis]

I think you need to treat your NULL value as Zeros. Have a look at the NZ function in the help.

[Jinx]

Thank you ... Thanks a lot. It really helped as I have a lot of NULL values in my table and I did not want to replace them with ZEROs.

I appreacite your help..

Jinx