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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
| Set xlSheet = classeur.Worksheets.Add
xlSheet.Name = "Tranche_Dep"
DoCmd.RunSQL "CREATE TABLE temptranch (prix double);"
DoCmd.RunSQL "INSERT INTO temptranch(prix) SELECT gsm.prix FROM gsm WHERE ((gsm.centre='" & cmb_dep.Column(2) & "') And (gsm.ntrim='" & cmb_tri_dep & "') And (gsm.annee='" & cmb_an_dep.Column(1) & "') And (gsm.blackb=False));"
DoCmd.RunSQL "INSERT INTO temptranch(prix) SELECT gsm.prix FROM gsm, departement, centre WHERE ((departement.num='" & cmb_dep.Column(2) & "') and (gsm.centre=centre.num) And (departement.id=centre.rel) And (gsm.ntrim='" & cmb_tri_dep & "') And (gsm.annee='" & cmb_an_dep.Column(1) & "') And (gsm.blackb=False));"
qr9 = "SELECT count(temptranch.prix) FROM temptranch WHERE (temptranch.prix<=50);"
rst9.Open qr9, CurrentProject.Connection, adOpenStatic
tranche9 = rst9.Fields(0)
qr10 = "SELECT count(temptranch.prix) FROM temptranch WHERE ((temptranch.prix>50) AND (temptranch.prix<=100));"
rst10.Open qr10, CurrentProject.Connection, adOpenStatic
tranche10 = rst10.Fields(0)
qr11 = "SELECT count(temptranch.prix) FROM temptranch WHERE ((temptranch.prix>100) AND (temptranch.prix<=150));"
rst11.Open qr11, CurrentProject.Connection, adOpenStatic
tranche11 = rst11.Fields(0)
qr12 = "SELECT count(temptranch.prix) FROM temptranch WHERE ((temptranch.prix>150) AND (temptranch.prix<=200));"
rst12.Open qr12, CurrentProject.Connection, adOpenStatic
tranche12 = rst12.Fields(0)
qr13 = "SELECT count(temptranch.prix) FROM temptranch WHERE ((temptranch.prix>200) AND (temptranch.prix<=250));"
rst13.Open qr13, CurrentProject.Connection, adOpenStatic
tranche13 = rst13.Fields(0)
qr14 = "SELECT count(temptranch.prix) FROM temptranch WHERE ((temptranch.prix>250) AND (temptranch.prix<=300));"
rst14.Open qr14, CurrentProject.Connection, adOpenStatic
tranche14 = rst14.Fields(0)
qr15 = "SELECT count(temptranch.prix) FROM temptranch WHERE ((temptranch.prix>300) AND (temptranch.prix<=350));"
rst15.Open qr15, CurrentProject.Connection, adOpenStatic
tranche15 = rst15.Fields(0)
qr16 = "SELECT count(temptranch.prix) FROM temptranch WHERE ((temptranch.prix>350) AND (temptranch.prix<=400));"
rst16.Open qr16, CurrentProject.Connection, adOpenStatic
tranche16 = rst16.Fields(0)
qr17 = "SELECT count(temptranch.prix) FROM temptranch WHERE (temptranch.prix>400);"
rst17.Open qr17, CurrentProject.Connection, adOpenStatic
tranche17 = rst17.Fields(0)
xlApp.Cells(2, 1) = "De 0 à 50"
xlApp.Cells(3, 1) = "De 50 à 100"
xlApp.Cells(4, 1) = "De 100 à 150"
xlApp.Cells(5, 1) = "De 150 à 200"
xlApp.Cells(6, 1) = "De 200 à 250"
xlApp.Cells(7, 1) = "De 250 à 300"
xlApp.Cells(8, 1) = "De 300 à 350"
xlApp.Cells(9, 1) = "De 350 à 400"
xlApp.Cells(10, 1) = "Supérieur à 400"
xlApp.Cells(2, 2) = tranche9
xlApp.Cells(3, 2) = tranche10
xlApp.Cells(4, 2) = tranche11
xlApp.Cells(5, 2) = tranche12
xlApp.Cells(6, 2) = tranche13
xlApp.Cells(7, 2) = tranche14
xlApp.Cells(8, 2) = tranche15
xlApp.Cells(9, 2) = tranche16
xlApp.Cells(10, 2) = tranche17
If cmb_tri_dep.Value = "1" Then
vartrim = "Premier"
ElseIf cmb_tri_dep.Value = "2" Then
vartrim = "Deuxième"
ElseIf cmb_tri_dep.Value = "3" Then
vartrim = "Troisième"
Else
vartrim = "Quatrième"
End If
xlApp.Charts.Add
xlApp.ActiveChart.ChartType = xl3DPie
xlApp.ActiveChart.SetSourceData Source:=classeur.Sheets("Tranche_Dep").Range("A2:B10"), PlotBy:=xlColumns
xlApp.ActiveChart.HasTitle = True
xlApp.ActiveChart.ChartTitle.Characters.Text = "Statistique du prix de l'abonnement par tranche pour le departement " & cmb_dep.Column(1) & " (" & vartrim & " trimestre " & cmb_an_dep.Column(1) & ")"
xlApp.ActiveChart.Location WHERE:=xlLocationAsObject, Name:="Tranche_Dep"
xlApp.ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, LegendKey:=False, HasLeaderLines:=True
xlApp.ActiveChart.HasLegend = True |
Partager