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 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212
|
Option Compare Database
Option Explicit
Private Sub Détail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
'---------------------
'Données de l'individu
'---------------------
Me.valIndividu = DLookup("[" & Me.txtCanCode & "]", "[TOUS RATIOS en K]", "" _
& "concess=""" & Reports![eRatios].[txtIndividu] & """ AND [Année N bilan]=" & Forms![fSelection].[txtAnnee])
'-------------------
'Données du National
'-------------------
'Création tEchantillon
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT [" & Me.txtCanCode & "] AS Donnees INTO tEchantillon " _
& "FROM [TOUS RATIOS en K_NATIONAL] " _
& "ORDER BY [" & Me.txtCanCode & "];"
DoCmd.SetWarnings True
If Me.txtFormatCol = "Percent" Then ' Les pourcentages ne sont agrégeables,on va chercher Q1 , Q3 et valeur mediane (AgregationPourcent)
For Each ctl In Me.Controls
If ctl.Name Like "Nl*" Then
Me(ctl.Name).Format = txtFormatCol
Me.valIndividu.Format = Me.txtFormatCol
Call AgregationPourcent("Nl")
End If
Next ctl
Else
For Each ctl In Me.Controls
If ctl.Name Like "Nl*" Then
Me(ctl.Name).Format = txtFormatCol
Me.valIndividu.Format = Me.txtFormatCol
Call Agregation("Nl")
End If
Next ctl
End If
'---------------------------
'Données du Groupe Typologie
'---------------------------
'Création tEchantillon
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT [" & Me.txtCanCode & "] AS Donnees INTO tEchantillon " _
& "FROM [TOUS RATIOS en K_TYPO] " _
& "WHERE [typologie] = [Forms]![fSelection]![txttypologie]" _
& "ORDER BY [" & Me.txtCanCode & "];"
DoCmd.SetWarnings True
If Me.txtFormatCol = "Percent" Then ' Les pourcentages ne sont agrégeables,on va chercher Q1 , Q3 et valeur mediane (AgregationPourcent)
For Each ctl In Me.Controls
If ctl.Name Like "Typo*" Then
Me(ctl.Name).Format = txtFormatCol
Me.valIndividu.Format = Me.txtFormatCol
Call AgregationPourcent("Typo")
End If
Next ctl
Else
For Each ctl In Me.Controls
If ctl.Name Like "Typo*" Then
Me(ctl.Name).Format = txtFormatCol
Me.valIndividu.Format = Me.txtFormatCol
Call Agregation("Typo")
End If
Next ctl
End If
'--------------------------
'Données du Groupe Régional
'--------------------------
'Création tEchantillon
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT [" & Me.txtCanCode & "] AS Donnees INTO tEchantillon " _
& "FROM [TOUS RATIOS en K_GROUPEREG] " _
& "WHERE [groupe regional] = [Forms]![fSelection]![txtCodeGroupe] " _
& "ORDER BY [" & Me.txtCanCode & "];"
DoCmd.SetWarnings True
If Me.txtFormatCol = "Percent" Then ' Les pourcentages ne sont agrégeables,on va chercher Q1 , Q3 et valeur mediane (AgregationPourcent)
For Each ctl In Me.Controls
If ctl.Name Like "Gr*" Then
Me(ctl.Name).Format = txtFormatCol
Me.valIndividu.Format = Me.txtFormatCol
Call AgregationPourcent("Gr")
End If
Next ctl
Else
For Each ctl In Me.Controls
If ctl.Name Like "Gr*" Then
Me(ctl.Name).Format = txtFormatCol
Me.valIndividu.Format = Me.txtFormatCol
Call Agregation("Gr")
End If
Next ctl
End If
'---------------------------
'Données du Groupe Effectif
'---------------------------
'Création tEchantillon
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT [" & Me.txtCanCode & "] AS Donnees INTO tEchantillon " _
& "FROM [TOUS RATIOS en K_EFF] " _
& "WHERE [groupe_effectif] = [Forms]![fSelection]![txteffectif]" _
& "ORDER BY [" & Me.txtCanCode & "];"
DoCmd.SetWarnings True
If Me.txtFormatCol = "Percent" Then ' Les pourcentages ne sont agrégeables,on va chercher Q1 , Q3 et valeur mediane (AgregationPourcent)
For Each ctl In Me.Controls
If ctl.Name Like "Eff*" Then
Me(ctl.Name).Format = txtFormatCol
Me.valIndividu.Format = Me.txtFormatCol
Call AgregationPourcent("Eff")
End If
Next ctl
Else
For Each ctl In Me.Controls
If ctl.Name Like "Eff*" Then
Me(ctl.Name).Format = txtFormatCol
Me.valIndividu.Format = Me.txtFormatCol
Call Agregation("Eff")
End If
Next ctl
End If
End Sub
Private Sub Report_Close()
DoCmd.Restore
End Sub
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
End Sub
Public Sub Agregation(Groupe As String)
Dim rst As Recordset
Dim i As Integer
Dim iNbre As Integer
'Création du record set
Set rst = CurrentDb.OpenRecordset("tEchantillon")
If rst.RecordCount = 0 Then
Me(Groupe & "Moy") = "-"
Me(Groupe & "MQ1") = "-"
Me(Groupe & "MQ4") = "-"
Exit Sub
End If
'Moyenne
Me(Groupe & "Moy") = 0
Do Until rst.EOF
Me(Groupe & "Moy") = Me(Groupe & "Moy") + rst.Fields(0)
rst.MoveNext
Loop
Me(Groupe & "Moy") = Me(Groupe & "Moy") / rst.RecordCount
'Moyenne des Q1 et Q4
'Nbre d'enregistrements du quartile
iNbre = rst.RecordCount \ 4 'l'opérateur \ donne la partie entière du quotient
If iNbre = 0 Then iNbre = 1
'Moyenne des Q1
rst.MoveFirst
Me(Groupe & "MQ1") = 0
For i = 1 To iNbre
Me(Groupe & "MQ1") = Me(Groupe & "MQ1") + rst.Fields(0)
rst.MoveNext
Next i
Me(Groupe & "MQ1") = Me(Groupe & "MQ1") / iNbre
'Moyenne des Q4
rst.MoveLast
Me(Groupe & "MQ4") = 0
For i = 1 To iNbre
Me(Groupe & "MQ4") = Me(Groupe & "MQ4") + rst.Fields(0)
rst.MovePrevious
Next i
Me(Groupe & "MQ4") = Me(Groupe & "MQ4") / iNbre
'Libérer
rst.Close
Set rst = Nothing
End Sub
Public Sub AgregationPourcent(Groupe As String)
Dim rst As Recordset
'Création du record set
Set rst = CurrentDb.OpenRecordset("tEchantillon")
If rst.RecordCount = 0 Then
Me(Groupe & "Moy") = "-"
Me(Groupe & "MQ1") = "-"
Me(Groupe & "MQ4") = "-"
Exit Sub
End If
'valeurMoyenne
rst.PercentPosition = 50
If rst.RecordCount Mod 2 = 0 Then rst.MovePrevious
Me(Groupe & "Moy") = rst.Fields(0)
'valeur Q1, valeur Q3
rst.PercentPosition = 25
If rst.RecordCount Mod 4 = 0 Then rst.MovePrevious
Me(Groupe & "MQ1") = rst.Fields(0)
rst.PercentPosition = 75
If rst.RecordCount Mod 4 = 0 Then rst.MovePrevious
Me(Groupe & "MQ4") = rst.Fields(0)
'Libérer
rst.Close
Set rst = Nothing
End Sub |
Partager