Dim req0 As ADODB.Recordset 'Efface la table des attestations
Dim req1 As ADODB.Recordset 'Récupère les clients qui ont eu des interventions
Dim req2 As ADODB.Recordset 'Récupère les données des interventions d'un client précis
Set req0 = New ADODB.Recordset
Set req1 = New ADODB.Recordset
Set req2 = New ADODB.Recordset
sql0 = "DELETE * FROM attestation;"
:fleche: req0.Open sql0, adoConnect, adKeySet, adLockOptimistic, adCmdText
sql1 = "SELECT c.id_c AS id FROM client c, intervenir intr WHERE c.id_c=intr.id_c AND YEAR(date)>(YEAR(now())-1) AND YEAR(date)<(YEAR(now())+1) GROUP BY c.id_c ORDER BY c.id_c"
req1.Open sql1, adoConnect, adKeySet, adLockOptimistic, adCmdText
While Not req1.EOF
sql2 = "SELECT date, modepaiement, intt.nom AS nom, sum(ints.duree) AS duree, sum(ints.prix_prest) AS prest FROM intervenir AS intr, interventions AS ints, intervenant AS intt, comprendre AS com WHERE intr.id_intr = com.id_intr AND com.id_i=ints.id_i AND intr.id_intt=intt.id_intt AND intr.id_c=" & req1!id & " GROUP BY date, modepaiement, intt.nom ORDER BY date"
req2.Open sql2, adoConnect, adKeySet, adLockOptimistic, adCmdText
chAttest_dates = ""
chAttest_paies = ""
chAttest_couts = ""
chAttest_intts = ""
dur_tot = 0
cout_tot = 0
While Not req2.EOF
dur_tot = dur_tot + req2!duree
cout_tot = cout_tot + req2!prest
chAttest_dates = chAttest_dates & req2!date & vbCrLf
chAttest_paies = chAttest_paies & req2!modepaiement & vbCrLf
chAttest_couts = chAttest_couts & req2!prest & vbCrLf
chAttest_intts = chAttest_intts & req2!Nom & vbCrLf
req2.MoveNext
Wend
sql3 = "INSERT INTO attestation ( id_c, total_duree, total_prest, modepaiement_inter, date_inter, cout_inter, intervenant) VALUES ( " & req1!id & ", " & dur_tot & ", " & cout_tot & ", " & req2!paies & ", " & req2!dates & ", " & req2!couts & ", " & req2!intts & "
;"
DoCmd.RunSQL sql3
req1.MoveNext
Wend
req2.Close
req1.Close
req0.Close