• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

Calculation of Average in SQL Statement

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.
Please your advice or (even better) solution how to solve this problem.
Thank you very much.
Kind regards
Private Sub Start_Command_Click()
Dim col As Integer
Dim row As Integer
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
 
stDB = "MY path to database\db2.mdb"
 
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;" _
& "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

Open in new window

0
garfild_1
Asked:
garfild_1
  • 2
1 Solution
 
hiteshgoldeneyeCommented:
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
 
garfild_1Author Commented:
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.
Please help...
Regards.
0
 
hiteshgoldeneyeCommented:
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
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
 
stDB = "MY path to database\db2.mdb"
 
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;" _
& "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
 

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now