I wanted to know whether the below can be submitted in any other way simply. I'm inserting the multiple values from a table "moncal" into the table "cumulative"(which calculates the average of the attendace of the student for all the subjects). Moncal table has totlec(total lecture) and totpre(total lecture the student present) values and from that, I am calculating the average of attendance of all the students. So, while fetching the values from 'moncal' I will get multiple values for a particular "rollno" and which will be stored in a variable a1,a2..... and b1,b2.... So, is there any way of declaring variable simply and automatically from this.
This is the code I've used:
Dim avg As Double
Set p1 = New ADODB.Recordset
p1.Open "select distinct(rollno) from moncal order by rollno asc", conn()
For rollno = 1 To p1.RecordCount
Set p2 = New ADODB.Recordset
p2.Open "select totpre,subcode from moncal where rollno = " & p1.Fields(0) & " order by subcode asc ", conn()
tot1 = 0
tot2 = 0
'avg = 0
a1 = 0
a2 = 0
a3 = 0
a4 = 0
a5 = 0
a6 = 0
a7 = 0
a8 = 0
a9 = 0
a10 = 0
a11 = 0
a12 = 0
a13 = 0
a14 = 0
a15 = 0
b1 = 0
b2 = 0
b3 = 0
b4 = 0
b5 = 0
b6 = 0
b7 = 0
b8 = 0
b9 = 0
b10 = 0
b11 = 0
b12 = 0
b13 = 0
b14 = 0
b15 = 0
For x = 1 To p2.RecordCount
If x = 1 Then
a1 = p2.Fields(0)
ElseIf x = 2 Then
a2 = p2.Fields(0)
ElseIf x = 3 Then
a3 = p2.Fields(0)
ElseIf x = 4 Then
a4 = p2.Fields(0)
ElseIf x = 5 Then
a5 = p2.Fields(0)
ElseIf x = 6 Then
a6 = p2.Fields(0)
ElseIf x = 7 Then
a7 = p2.Fields(0)
ElseIf x = 8 Then
a8 = p2.Fields(0)
ElseIf x = 9 Then
a9 = p2.Fields(0)
ElseIf x = 10 Then
a10 = p2.Fields(0)
ElseIf x = 11 Then
a11 = p2.Fields(0)
ElseIf x = 12 Then
a12 = p2.Fields(0)
ElseIf x = 13 Then
a13 = p2.Fields(0)
ElseIf x = 14 Then
a14 = p2.Fields(0)
ElseIf x = 15 Then
a15 = p2.Fields(0)
ElseIf x = 16 Then
a16 = p2.Fields(0)
ElseIf x = 17 Then
a17 = p2.Fields(0)
ElseIf x = 18 Then
a18 = p2.Fields(0)
ElseIf x = 19 Then
a19 = p2.Fields(0)
ElseIf x = 20 Then
a20 = p2.Fields(0)
Else
Exit For
p2.MoveNext
Next
Set p3 = New ADODB.Recordset
p3.Open "select totlec,subcode from moncal where rollno = " & p1.Fields(0) & " order by subcode asc ", conn()
For y = 1 To p3.RecordCount
If y = 1 Then
b1 = p3.Fields(0)
ElseIf y = 2 Then
b2 = p3.Fields(0)
ElseIf y = 3 Then
b3 = p3.Fields(0)
ElseIf y = 4 Then
b4 = p3.Fields(0)
ElseIf y = 5 Then
b5 = p3.Fields(0)
ElseIf y = 6 Then
b6 = p3.Fields(0)
ElseIf y = 7 Then
b7 = p3.Fields(0)
ElseIf y = 8 Then
b8 = p3.Fields(0)
ElseIf y = 9 Then
b9 = p3.Fields(0)
ElseIf y = 10 Then
b10 = p3.Fields(0)
ElseIf y = 11 Then
b11 = p3.Fields(0)
ElseIf y = 12 Then
b12 = p3.Fields(0)
ElseIf y = 13 Then
b13 = p3.Fields(0)
ElseIf y = 14 Then
b14 = p3.Fields(0)
ElseIf y = 15 Then
b15 = p3.Fields(0)
ElseIf y = 16 Then
b16 = p3.Fields(0)
ElseIf y = 17 Then
b17 = p3.Fields(0)
ElseIf y = 18 Then
b18 = p3.Fields(0)
ElseIf y = 19 Then
b19 = p3.Fields(0)
ElseIf y = 20 Then
b20 = p3.Fields(0)
Else
Exit For
End If
p3.MoveNext
Next
roll = p1.Fields(0)
tot1 = a1 + a2 + a3 + a4 + a5 + a6 + a7 + a8 + a9 + a10 + a11 + a12 + a13 + a14 + a15 + a16 + a17 + a18 + a19 + a20
tot2 = b1 + b2 + b3 + b4 + b5 + b6 + b7 + b8 + b9 + b10 + b11 + b12 + b13 + b14 + b15 + b16 + b17 + b18 + b19 + b20
avg = tot1 / tot2 * 100
Thanks in advance.
You don't need all of those variables if the aim is only to sum the values...
Dim avg As Double
Set p1 = New ADODB.Recordset
p1.Open "select distinct(rollno) from moncal order by rollno asc", conn()
For rollno = 1 To p1.RecordCount
roll = p1.Fields(0)
Set p2 = New ADODB.Recordset
p2.Open "select totpre,totlec,subcode from moncal where rollno = " & roll & " order by subcode asc ", conn()
tot1 = 0
tot2 = 0
For x = 1 To p2.RecordCount
tot1 = tot1 + p2.Fields(0).Value
tot2 = tot2 + p2.Fields(1).Value
If x = 20 Then Exit For
p2.MoveNext
Next
avg = tot1 / tot2 * 100
Debug.Print rollno, avg
Next rollno