أخواني الأعزاء لقد توصلت إلى الحل وإليكم الكود
تقبلوا تحياتي
كود :
Try
Conn.Open()
SQLStr = "CREATE VIEW r AS SELECT A.name, IIF(B.Cnt IS NULL,0,B.Cnt) AS [Total Approved] FROM (SELECT DISTINCT name FROM MyTable) A LEFT JOIN (SELECT name,COUNT(result) AS Cnt FROM MyTable WHERE result='yes' GROUP BY name) B ON A.name = B.name"
Dim cmd1 As New OleDbCommand(SQLStr, Conn)
cmd1.ExecuteNonQuery()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SQLStr = "SELECT MyTable.name AS [Name], Count(MyTable.subject) AS [Total Subjects], [Total Approved], IIf([Total Approved] = 0, 0, IIf([Total Approved] = 1, '" & TB_S1.Text & "', IIf([Total Approved] = 2, '" & TB_S2.Text & "', IIf([Total Approved] = 3, '" & TB_S3.Text & "', IIf([Total Approved] = 4, '" & TB_S4.Text & "', IIf([Total Approved] = 5, '" & TB_S5.Text & "', IIf([Total Approved] > 5 , [Total Approved], ))))))) As Points FROM MyTable INNER JOIN r ON MyTable.name = r.name GROUP BY MyTable.name, [Total Approved]"
Dim dp As New OleDbDataAdapter(SQLStr, Conn)
Dim ds As New DataSet
dp.Fill(ds)
DGV_Statistics.DataSource = ds.Tables(0)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SQLStr = "drop VIEW r"
Dim cmd2 As New OleDbCommand(SQLStr, Conn)
cmd2.ExecuteNonQuery()
Conn.Close()
Catch ex As Exception
Conn.Close()
MsgBox(ex.Message, MsgBoxStyle.Critical, " Error")
End Tryتقبلوا تحياتي
{ اللهم علمنا ما ينفعنا وانفعنا بما علمتنا إنك أنت العليم الخبير }