Handling null values when calclulating the SUM of Columns?

[savedlema]

Hi all!
I have a table with (StudentID,Physics,Chemistry,Total,Average)
I want to pull values of Physics,Chemistry and calculate Total and then Average for a specific StudentID. With the following code:

"SELECT SUM (IIF(ISNULL([Physics]),0,[Physics])) As Phy, SUM (IIF(ISNULL([Chemistry]),0,[Chemistry])) As Chem, SUM([Physics])+Sum([Chemistry]) as Total FROM tblForm1 WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

I get the following:

1. If all the values exists in the table, everything is fine, it fetches the values and calculates the sum.
2. But if one or both values are null (not filled) they are replaced with '0' but it does not do the Sum (calculate the total).

Can someone please show me the way through this? I want it to calculate the sum even if one or both values are null (the total will then be '0')

I will appreciate any inputs please. (I use an Access .mdb database)

[Reverend Jim]

Try the following. I don't have a sample database in Access so I haven't tried this query. I used SQL with the COALESCE function (does the same thing as NZ), then did a copy/paste.

"SELECT StudentID, NZ(Physics,0) + NZ(English,0) + NZ(Chemistry,0) + " & _
"                  NZ(Biology,0) + NZ(History,0) AS total " & _
"  FROM tblClass1 " & _
" WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

[Reverend Jim]

You could also set the default value for any mark field to 0 so that NULLS are never encountered.

[adam_k]

Reverend's NZ will probably help you, but just to make it clear why sum won't work:
Your code:

"SELECT SUM (IIF(ISNULL([Physics]),0,[Physics])) As Phy, SUM (IIF(ISNULL([Chemistry]),0,[Chemistry])) As Chem, SUM([Physics])+Sum([Chemistry]) as Total FROM tblForm1 WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

does replace null values in column physics with 0 and returns it as column Phy and replaces null values in column chemistry with 0 and returns it as Chem. Your sum is reading the "original" columns Phy and Chem, including their null values and won't work.
In order for this to work you need to use the same approach iif(isnull(column,0,column) inside the sum:

"SELECT SUM (IIF(ISNULL([Physics]),0,[Physics])) As Phy, SUM (IIF(ISNULL([Chemistry]),0,[Chemistry])) As Chem, SUM(IIF(ISNULL([Physics]),0,[Physics]))+Sum(IIF(ISNULL([Chemistry]),0,[Chemistry])) as Total FROM tblForm1 WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

This way you are displaying zeros AND calculating zeros instead of NULLs.

[Reverend Jim]

Also, SUM doesn't add up the values inside the parentheses.

SELECT SUM(Physics) FROM tblClass1

Will give you the total of all the Physics marks for all students. It SUMS down the rows rather than across the columns.

[savedlema]

Thank you great friends! The trick however, was that NZ works only within Access database and can't be used from VB. The solution was to use (IIF(ISNULL to all of my code, even to calculating SUM. I hope the thread might be helpful to others.

Thanks all, you are great!

[Reverend Jim]

I didn't realize until I looked it up that you can only use NZ if the Expression Service is available. And the Expression Service is only available from within an Access application instance. Which means that queries from VB have to use the following instead

"SELECT StudentID, IIF(Physics   IS NULL,0,Physics)   + " & _
                 " IIF(English   IS NULL,0,English)   + " & _
                 " IIF(Chemistry IS NULL,0,Chemistry) + " & _
                 " IIF(Biology   IS NULL,0,Biology)   + " & _
                 " IIF(History   IS NULL,0,History) AS total " & _
"  FROM tblClass1 " & _
" WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

That's just retarded.

[savedlema]

Thanks Reverend!
Please, I want to INSERT the 'Total' obtained by this query into another field in the SAME TABLE. I have tried the following:

INSERT INTO Total(this is a column) SELECT ...

Returned "Could not find output table 'Total'." Error msg.

And,

INSERT INTO [tblForm1.Total] SELECT ...

Returned "Could not find output table 'tblForm1.Total'." Error msg.

And,

INSERT INTO tblForm1 (total) SELECT

returned "Number of query values and destination fields are not the same."

INSERT INTO works fine when moving values from one table to another but hasn't from my case.

Anyway to do that? PS:I thought its good to join this here rather than starting a new thread.

[Reverend Jim]

INSERT adds a new record. What you want to do is UPDATE a field in an existing record. Tp update all the records at once you do

"UPDATE tblClass1 " & _
"   SET Total = IIF(Physics   IS NULL,0,Physics)   + " & _
"               IIF(English   IS NULL,0,English)   + " & _
"               IIF(Chemistry IS NULL,0,Chemistry) + " & _
"               IIF(Biology   IS NULL,0,Biology)   + " & _
"               IIF(History   IS NULL,0,History)"

or if you just want to update specific records you can add a WHERE clause at the end as in

"UPDATE tblClass1 " & _
"   SET Total = IIF(Physics   IS NULL,0,Physics)   + " & _
"               IIF(English   IS NULL,0,English)   + " & _
"               IIF(Chemistry IS NULL,0,Chemistry) + " & _
"               IIF(Biology   IS NULL,0,Biology)   + " & _
"               IIF(History   IS NULL,0,History)   + " & _
" WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "    

Incidentally, I missed a couple of line continuation characters at the ent of lines in the previous post (I put them back in). I'm sure you caught that when you tried it.

PS:I thought its good to join this here rather than starting a new thread.

No problem.

[savedlema]

Thanks Reverend.
Can you tell what is wrong with this?

"UPDATE tblClass1 SET Total= SUM (IIF(ISNULL([Physics]),0,[Physics])) As Phy, " _
                    + "SUM(IIF(ISNULL([Chemistry]),0,[Chemistry])) As Chem," _
                    + "SUM(IIF(ISNULL([Civics]),0,[Civics])) As Civ, " _
                    + "SUM(IIF(ISNULL([Biology]),0,[Biology])) As Bios," _
                    + "SUM(IIF(ISNULL([Geography]),0,[Geography])) As Geo, " _
                    + "SUM(IIF(ISNULL([Physics]),0,[Physics]))+ " _
                    + "SUM(IIF(ISNULL([Chemistry]),0,[Chemistry]))+ " _
                    + "SUM(IIF(ISNULL([Civics]),0,[Civics]))+ " _
                    + "SUM(IIF(ISNULL([Biology]),0,[Biology]))+ " _
                    + "SUM(IIF(ISNULL([Geography]),0,[Geography])) As Total FROM tblForm1 " _
                    + "WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

The part of the code without "UPDATE tblClass1 SET Total=.." Works fine, (I mean with the INSERT) So, the rest of the code has no problem.
The error msg is "Syntax error (missing operator) in query expression 'SUM(IIF(ISNULL([Physics]),0,[Physics])) As Phy'."

[Reverend Jim]

Don't use SUM. That is used to return the result of adding up several records. For example, if you had a table of sales data and you wanted to know the total sales for the Eastern region you would code

SELECT SUM(Sales) AS TotalSales 
  FROM Sales_Table
 WHERE Region = 'Eastern'

What you want to do is calculate the Total of the individual marks for each record. If we ignore the test for NULL values the query is

UPDATE tblClass1 SET Total = Physics+English+Chemistry+Biology+History

You don't need (or want) to do either of the following (both of which are invalid)

UPDATE tblClass1 SET Total = SUM(Physics+English+Chemistry+Biology+History)
UPDATE tblClass1 SET Total = SUM(Physics,English,Chemistry,Biology,History)

[savedlema]

Reverend,
I tried you code, tested for NULL values and it works very well. And the code isn't that bulky!
I would like to thank you sincerely for your geneorous support.
I hope the thread will be useful to others too.
Thank you so much.

[Reverend Jim]

Glad I could help.

[savedlema]

I had used another long long way to accomplish that, But your solution was the perfect one.