13,095,921 members (53,251 online)
Rate this:
See more:
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:
If all the values exists in the table, everything is fine, it fetches the values and calculates the sum. 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)
Posted 14-Nov-12 2:22am
Updated 14-Nov-12 2:59am
v5

Rate this:

## Solution 2

Hi,

You are doing so much its simple.

```
select	ISNULL(Physics,0) as Physics,
ISNULL(Chemistry,0) as Chemistry,
SUM(ISNULL(Physics,0)+ISNULL(Chemistry,0)) as Total,
(SUM(ISNULL(Physics,0)+ISNULL(Physics,0))/2) as [Avg]
FROM	tblForm1
WHERE	StudentID = '" & Trim(Me.txtSearch.Text) & "'"
```

Thanks
v2
savedlema 16-Nov-12 5:28am

Thanks Ved, I have a question please;
I want to INSERT the result of this query (Total) into a field called "TotalMarks" found in the same table. How to I do that?
I tried INSERT INTO TotalMarks SELECT...(previous solution query) but did not work.
I also tried INSET INTO tblForm1.TotalMarks but never worked.

Thanks a lot so far.
Rate this:

## Solution 3

Hi

Try This

```Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim dbTableName As String
Dim sql1 As String
Dim MaxRows As Integer

dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = "
'Define these two variables
dbSource = "C:/Marks.mdb"
dbTableName = "Science_Marks"

con.ConnectionString = dbProvider & dbSource

Dim ds As New DataSet
sql1="SELECT * FROM " & dbTableName

con.Open()
da = New OleDb.OleDbDataAdapter( sql, con )
da.Fill(ds, "FancyName")
MaxRows = ds.Tables("FancyName").Rows.Count
con.Close()

For i As Integer = 0 To MaxRows - 1
Dim physics As Double = 0
Dim chemistry As Double = 0
Try
physics = ds.Tables("FancyName").Rows(i).Item(1)
Catch exp As Exception
End Try
Try
chemistry = ds.Tables("FancyName").Rows(i).Item(2)
Catch exp2 As Exception
End Try
Dim total As Double = physics + chemistry
Dim average As Double = total / 2
'Do Whatever with the total and average
Next```

This will stop the sql injection problem.

Jacques
v3
savedlema 16-Nov-12 5:30am

Thanks Jacques, Your insert is new to me, so I will have to study it, I know its gonna help me a great deal.
Thanks.
Jacques Loubser 16-Nov-12 7:40am

There is no insert code. To Insert the values to the table do the following:
Replace the 'Do Whatever with:
ds.Tables("FancyName").Rows(i).Item(3) = average.toString();
ds.Tables("FancyName").Rows(i).Item(4) = total.toString();
After the Next put:
da.Update(ds, "FancyName")
Move the con.Close() to the end & then add
con.Dispose()
Rate this:

## Solution 1

Try this, I am not sure if this will work in Access, as I am far more used to MSSQL2005 and above.

```SELECT
SUM(ISNULL([Physics],0)) AS Phy,
SUM(ISNULL([Chemistry],0)) AS Chem,
SUM(ISNULL([Physics],0)) + SUM (ISNULL([Chemistry],0)) AS Total
FROM tblForm1
WHERE RTRIM(StudentID) = 'STUDENT1'
GROUP BY [Physics]
,[Chemistry]```

Just paste that into your SQL and then replace 'STUDENT1' with your '" & Trim(Me.txtSearch.Text) & "'" but.... I would be a bit concerned about using it as Dynamic SQL from the VB, as the user could enter SQL Injection to cause some trouble!

Try and use Stored Procedures with Parameters... As I am lead to believe this is safer....
v2
savedlema 16-Nov-12 5:31am

Thanks Rob,

I want to INSERT the result of this query (Total) into a field called "TotalMarks" found in the same table. How to I do that?
I tried INSERT INTO TotalMarks SELECT...(the select query) but did not work.
I also tried INSET INTO tblForm1.TotalMarks SELECT .... but never worked.

Thanks a lot so far.
Rob@Love2Code 16-Nov-12 8:25am

I would rather have a table of marks against that user /student.
So MarkID, StudentID, Mark

and then do a sum based on that table.

That is how I would do it.
Rate this:

## Solution 4

Thank you all. I have solved the problem.
Many blessings to you.

Top Experts
Last 24hrsThis month
 OriginalGriff 168 Jochen Arndt 95 Karthik Bangalore 65 Richard MacCutchan 60 Mohibur Rashid 59
 OriginalGriff 4,131 Graeme_Grant 2,232 ProgramFOX 2,057 Jochen Arndt 1,735 ppolymorphe 1,735

Web02 | 2.8.170813.1 | Last Updated 16 Nov 2012