1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
| Imports System.Data
Imports System.Data.OleDb
Public Class Form1
Dim strcon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & Application.StartupPath & "\notes.mdb"
Dim con As New OleDbConnection
Dim strSQL As String = "SELECT e.idelv, e.nom, e.pren ,
m.libmat , t.libtrim,
((t1.moycc * 2) + t2.note_comp) / 5 AS Moyenne
FROM
(
SELECT n1.idelv, c1.idmat, c1.idtrim,
AVG(n1.note) AS moycc
FROM evaluation n1
INNER JOIN controle c1 ON c1.idcontr = n1.idcontr
WHERE c1.typecontr <> 'comp'
GROUP BY n1.idelv, c1.idmat, c1.idtrim
) t1
INNER JOIN
(
SELECT n2.idelv, c2.idmat, c2.idtrim,
n2.note AS note_comp
FROM evaluation n2
INNER JOIN controle c2 ON c2.idcontr = n2.idcontr
WHERE c2.typecontr = 'comp'
) t2
ON t2.idelv = t1.idelv
AND t2.idmat = t1.idmat
AND t2.idtrim = t1.idtrim
INNER JOIN eleve e ON e.idelv = t1.idelv
INNER JOIN matiere m ON m.idmat = t1.idmat
INNER JOIN trimestre t ON t.idtrim = t1.idtrim"
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.Text = "Calcul des moyennes par élève et par matière"
con.ConnectionString = strcon
Dim da As New OleDbDataAdapter(strSQL, con)
Dim ds As New DataSet
con.Open()
da.Fill(ds, "evaluation")
con.Close()
dg.DataSource = ds
dg.DataMember = "evaluation"
dg.Columns("MoyMat").DefaultCellStyle.Format = "##.##"
End Sub
End Class |
Partager