Solved

# Calculation of Average in SQL Statement

Posted on 2009-02-18
703 Views
Hello,
I would appreciate help regarding this:
I have bellow VBA code which I use to select and export data from MS Access database to MS Excell worksheet. It works very fine. But I have problem because I need to set my bellow strSQL statement in way to have also AVERAGE calculated on D_1,D_2 and D_3 values within the same. It would (according to my understanding)look something like this:
strSQL = " SELECT ID, NAME, PP_NO, D_1, D_2, D_3, AVG(D_1,D_2,D_3) from TBL_1 WHERE DATE = " & Me.Combobox.Value& " "

Also I find As huge problem for me(that also must be incorporated in my VBA) is the fact that Average must not take into consideration D_1,D_2 or D_3 if some of them is Null. It means if D_1 = 4, D_2 = 5 and D_3 = 0 then average = (D_1+D_2)/2 because D_3 is Null. Otherwise, if for instance D_3 = 10 then average is (D_1+D_2+D_3)/3
This Average is calculated on level of each ID which are a number of aproximate 100 positions within the table and each one is unique.
Thank you very much.
Kind regards
``````Private Sub Start_Command_Click()

Dim col As Integer

Dim row As Integer

stDB = "MY path to database\db2.mdb"

& "Data Source=" & stDB & ";"

strSQL = " SELECT ID, NAME, PP_NO, D_1, D_2, D_3 from TBL_1 WHERE DATE = " & Me.Combobox.Value& " "

rst.Open strSQL, cnn

col = 0

row = 6

Do While Not rst.EOF

row = row + 1

col = 0

Do While col < rst.Fields.Count

Cells(row, col + 1) = rst.Fields(col).Value

col = col + 1

Loop

rst.MoveNext

Loop

Set rst = Nothing: Set cnn = Nothing

End Sub
``````
0
Question by:garfild_1

LVL 13

Expert Comment

Hi garfild_1

I understand that you will paste D_1 , D_2 and D_3 lets say in column C,D,E resp. in the excel sheet, so why  not calculate the average in the excel sheet with a formula =SUM(C1:E1)/COUNTIF(C1:E1,">0") for first ID and autofill for the rest

0

Author Comment

Hello,
Thank you for your reply in such short notice. Your solution is very practical but my boss doesn't want to see in future these 3 columns any more in woksheet (only result which is Average). That is why I need VBA solution. It is easy to exclude the D_1... D_3 fileds from SELECT statement but then I dont know how to calculate Average in my original VBA. Also hiding of columns in Excel Worksheet is not acceptable (as solution) so we are back again on my issue how to adjust VBA in order to calculate Average as described. Thank you very much for your proposition one more time but deffinatelly I need to have this solved within VBA code.
Regards.
0

LVL 13

Accepted Solution

ok how about using the following code then to obtain the average, you can put the avg whereever you want in the output sheet
``````Private Sub Start_Command_Click()

Dim col As Integer

Dim row As Integer

Dim avg as integer,sum as integer,total as integer

stDB = "MY path to database\db2.mdb"

& "Data Source=" & stDB & ";"

strSQL = " SELECT ID, NAME, PP_NO, D_1, D_2, D_3 from TBL_1 WHERE DATE = " & Me.Combobox.Value& " "

rst.Open strSQL, cnn

col = 0

row = 6

Do While Not rst.EOF

row = row + 1

col = 0

total=0

sum=0

Do While col < rst.Fields.Count

Cells(row, col + 1) = rst.Fields(col).Value

if col>=3 and col<=5

if rst.fields(col).value > 0 then

sum=sum+rst.fields(col).value

total=total+1

end if

end if

col = col + 1

Loop

if total>0 then

avg=sum/total

else

avg=0

end if

rst.MoveNext

Loop

Set rst = Nothing: Set cnn = Nothing

End Sub

``````
0

## Featured Post

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describesâ€¦
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦