12,510,988 members (57,648 online)
Rate this:
Please Sign up or sign in to vote.
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
Keith Barrow158.1K
v5

## 4 solutions

Rate this:
Please Sign up or sign in to vote.

## 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) & "'"
```

Hope, It will help you.
Thanks
v2
Comments
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.

Any idea please?

Thanks a lot so far.
Rate this:
Please Sign up or sign in to vote.

## 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
Dim da As OleDb.OleDbDataAdapter
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
Comments
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:
Please Sign up or sign in to vote.

## 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
Comments
savedlema 16-Nov-12 5:31am

Thanks Rob,

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...(the select query) but did not work.
I also tried INSET INTO tblForm1.TotalMarks SELECT .... but never worked.

Any idea please?

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:
Please Sign up or sign in to vote.

## Solution 4

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 David_Wimbley 240 OriginalGriff 216 John Simmons / outlaw programmer 195 Maciej Los 158 Midi_Mick 140
 OriginalGriff 8,074 Maciej Los 4,119 ppolymorphe 4,086 David_Wimbley 3,391 Karthik Bangalore 3,196

Advertise | Privacy | Mobile
Web02 | 2.8.160929.1 | Last Updated 16 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100