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
| DoCmd.SetWarnings True
Dim rs_der_def As DAO.Recordset
Dim rs_Proprio As DAO.Recordset
Dim temps_ref, temps_next As Integer
Dim synthèse As String
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from Test_alerte_Z2N"
DoCmd.RunSQL "delete * from Test_alerte_Z2N_2"
DoCmd.RunSQL "delete * from Test_alerte_Z2N_3"
DoCmd.RunSQL "delete * from Test_alerte_Z2N_4"
DoCmd.RunSQL "delete * from Test_alerte_Z2N_5"
DoCmd.RunSQL "delete * from new_test_Z2N"
DoCmd.RunSQL "delete * from new_test_Z2N_2"
sive2 = "SELECT [Parc Z2N].Rame_Complète, defauts_NAT.Date"
sive2 = sive2 & " FROM listing_defauts INNER JOIN (defauts_NAT INNER JOIN [Parc Z2N] ON defauts_NAT.Véhicule = [Parc Z2N].véhicule) ON listing_defauts.Code = defauts_NAT.code_defaut"
sive2 = sive2 & " GROUP BY [Parc Z2N].Rame_Complète, defauts_NAT.Date, defauts_NAT.exploitation, listing_defauts.vrai_defaut"
sive2 = sive2 & " HAVING (((defauts_NAT.Date)<#" & Now() & "# And (defauts_NAT.Date)>=#" & Now() & "#-4) AND ((listing_defauts.vrai_defaut)=True))" 'AND ((defauts_NAT.exploitation)=True)
Set rs_sive2 = CurrentDb.OpenRecordset(sive2)
If Not rs_sive2.EOF Then
rs_sive2.MoveLast
a = rs_sive2.RecordCount
If a > 0 Then
rs_sive2.MoveFirst
Do While Not rs_sive2.EOF
DoCmd.RunSQL "insert into test_alerte_Z2N(Rame,[Date Defaut]) values('" & rs_sive2("Rame_Complète").Value & "',#" & Format(rs_sive2("Date").Value, "mm/dd/yyyy") & "#)"
rs_sive2.MoveNext
Loop
Else
End If
engin = "SELECT test_alerte_Z2N.Rame"
engin = engin & " FROM test_alerte_Z2N"
engin = engin & " GROUP BY test_alerte_Z2N.Rame"
Set rs_engin = CurrentDb.OpenRecordset(engin)
If Not rs_engin.EOF Then
rs_engin.MoveLast
n = rs_engin.RecordCount
rs_engin.MoveFirst
Do While Not rs_engin.EOF
nom_fichier = ""
nom_fichier2 = ""
nom_fichier3 = ""
nom_fichier4 = ""
DoCmd.RunSQL "delete * from Test_alerte_Z2N_2"
DoCmd.RunSQL "delete * from Test_alerte_Z2N_3"
DoCmd.RunSQL "delete * from Test_alerte_Z2N_4"
DoCmd.RunSQL "delete * from Test_alerte_Z2N_5"
DoCmd.RunSQL "delete * from new_test_Z2N"
DoCmd.RunSQL "delete * from new_test_Z2N_2"
rame2 = ""
'***SIVE***
DoCmd.SetWarnings False
''''
rame2 = rs_engin("Rame").Value
sive3 = "SELECT Test_Alerte_Z2N.rame, Test_Alerte_Z2N.[Date defaut], Min(defauts_NAT.Première) AS MinDePremière, defauts_NAT.code_defaut, Sum(defauts_NAT.Occurrences) AS SommeDeOccurrences, listing_defauts.Systeme_sollector, defauts_NAT.localisation, listing_defauts.[Criticité SNCF], [Parc Z2N].Ets_Propriétaire"
sive3 = sive3 & " FROM Test_Alerte_Z2N INNER JOIN (listing_defauts INNER JOIN (defauts_NAT INNER JOIN [Parc Z2N] ON defauts_NAT.Véhicule = [Parc Z2N].véhicule) ON listing_defauts.Code = defauts_NAT.code_defaut) ON (Test_Alerte_Z2N.[Date defaut] = defauts_NAT.Date) AND (Test_Alerte_Z2N.Rame = [Parc Z2N].Rame_Complète)"
sive3 = sive3 & " GROUP BY test_alerte_Z2N.rame , Test_Alerte_Z2N.[Date defaut], defauts_NAT.code_defaut, listing_defauts.Systeme_sollector, defauts_NAT.localisation, listing_defauts.[Criticité SNCF], [Parc Z2N].Ets_Propriétaire"
sive3 = sive3 & " HAVING ((test_alerte_Z2N.rame like '" & rame2 & "') AND (listing_defauts.Systeme_sollector= '" & "SIV ET SAE" & "'))"
Set rs_sive3 = CurrentDb.OpenRecordset(sive3)
If Not rs_sive3.EOF Then
rs_sive3.MoveLast
b = rs_sive3.RecordCount
If b > 0 Then
rs_sive3.MoveFirst
Do While Not rs_sive3.EOF
DoCmd.RunSQL "insert into test_alerte_Z2N_2(Rame,[Date Défaut],[Première heure défaut],Système,[Code défaut],Occurrences, Localisation, Criticité,[Ets Propriétaire]) values('" & rs_sive3("Rame").Value & "',#" & Format(rs_sive3("Date defaut").Value, "mm/dd/yyyy") & "#,#" & Format(rs_sive3("MinDePremière").Value, "hh:mm:ss") & "#,'" & rs_sive3("Systeme_sollector").Value & "','" & rs_sive3("code_defaut").Value & "'," & rs_sive3("SommeDeOccurrences").Value & ",'" & rs_sive3("localisation").Value & "'," & rs_sive3("Criticité SNCF").Value & ",'" & rs_sive3("Ets_propriétaire").Value & "')"
DoCmd.RunSQL "UPDATE Test_alerte_Z2N_2 SET Test_alerte_Z2N_2.Localisation = Mid([Test_alerte_Z2N_2]![Localisation],1,7)"
rs_sive3.MoveNext
Loop
Else
End If
SQL_new_test_Z2N = "INSERT INTO new_test_Z2N ( Rame, [Date défaut], [Code Défaut], Système, Occurences, [Ets Propriétaire], Criticité, [Première heure défaut] )"
SQL_new_test_Z2N = SQL_new_test_Z2N & " SELECT Test_alerte_Z2N_2.Rame, Test_alerte_Z2N_2.[Date défaut], Test_alerte_Z2N_2.[Code Défaut], Test_alerte_Z2N_2.Système, Sum(Test_alerte_Z2N_2.Occurrences) AS SommeDeOccurrences, Test_alerte_Z2N_2.[Ets Propriétaire], Test_alerte_Z2N_2.Criticité, First(Test_alerte_Z2N_2.[Première heure défaut]) AS [PremierDePremière heure défaut]"
SQL_new_test_Z2N = SQL_new_test_Z2N & " From Test_alerte_Z2N_2"
SQL_New_Tes_Z2N = SQL_new_test_Z2N & " GROUP BY Test_alerte_Z2N_2.Rame, Test_alerte_Z2N_2.[Date défaut], Test_alerte_Z2N_2.[Code Défaut], Test_alerte_Z2N_2.Système, Test_alerte_Z2N_2.[Ets Propriétaire], Test_alerte_Z2N_2.Criticité"
DoCmd.RunSQL (SQL_New_Tes_Z2N)
DoCmd.RunSQL ("UPDATE new_test_Z2N INNER JOIN listing_defauts ON new_test_Z2N.[Code Défaut] = listing_defauts.Code SET new_test_Z2N.Description = [listing_defauts].[Intitulé], new_test_Z2N.Préconisations = [listing_defauts].[preconisation]")
Else
End If
SQL_Code = "SELECT new_test_Z2N.[code défaut]"
SQL_Code = SQL_Code & " FROM new_test_Z2N"
SQL_Code = SQL_Code & " GROUP BY new_test_Z2N.[code défaut]"
Set rs_SQL_Code = CurrentDb.OpenRecordset(SQL_Code)
If Not rs_SQL_Code.EOF Then
rs_SQL_Code.MoveLast
c = rs_SQL_Code.RecordCount
If c > 0 Then
rs_SQL_Code.MoveFirst
Do While Not rs_SQL_Code.EOF
code2 = rs_SQL_Code("Code défaut").Value
SQL_Localisation = "SELECT Sum(Test_alerte_Z2N_2.Occurrences) AS SommeDeOccurrences, Test_alerte_Z2N_2.Localisation, new_test_Z2N.[Code Défaut]"
SQL_Localisation = SQL_Localisation & " FROM Test_alerte_Z2N_2 INNER JOIN new_test_Z2N ON Test_alerte_Z2N_2.[Code Défaut] = new_test_Z2N.[Code Défaut]"
SQL_Localisation = SQL_Localisation & " GROUP BY Test_alerte_Z2N_2.Localisation, new_test_Z2N.[Code Défaut], Test_alerte_Z2N_2.[Code Défaut]"
SQL_Localisation = SQL_Localisation & " HAVING (('" & code2 & "' = [Test_alerte_Z2N_2].[Code Défaut]))"
Set rs_SQL_Localisation = CurrentDb.OpenRecordset(SQL_Localisation)
If Not rs_SQL_Localisation.EOF Then
rs_SQL_Localisation.MoveLast
d = rs_SQL_Localisation.RecordCount
If d > 0 Then
rs_SQL_Localisation.MoveFirst
Do While Not rs_SQL_Localisation.EOF
DoCmd.RunSQL "INSERT INTO new_test_Z2N_2 (SommeDeOccurrences, Localisation, [Code Défaut]) values (" & rs_SQL_Localisation("SommeDeOccurrences").Value & ", '" & rs_SQL_Localisation("Localisation").Value & "', '" & rs_SQL_Localisation("Code Défaut").Value & "')"
rs_SQL_Localisation.MoveNext
Loop
Else
End If
DoCmd.RunSQL "UPDATE new_test_Z2N INNER JOIN new_test_Z2N_2 ON new_test_Z2N.[Code Défaut] = new_test_Z2N_2.[Code Défaut] SET new_test_Z2N_2.Localisation = [new_test_Z2N_2].[SommeDeOccurrences] & "" "" & ""Occurrence(s) sur"" & "" "" & [new_test_Z2N_2].[localisation] & Chr(13) & Chr(10) WHERE (([new_test_Z2N_2].[Code Défaut]= '" & code2 & "'))"
Else
End If
SQL_synthèse = "SELECT new_test_Z2N_2.Localisation"
SQL_synthèse = SQL_synthèse & " FROM new_test_Z2N_2"
SQL_synthèse = SQL_synthèse & " WHERE (new_test_Z2N_2.[code défaut] = '" & code2 & "')"
Set rs_SQL_synthèse = CurrentDb.OpenRecordset(SQL_synthèse)
If Not rs_SQL_synthèse.EOF Then
rs_SQL_synthèse.MoveLast
e = rs_SQL_synthèse.RecordCount
If e > 0 Then
rs_SQL_synthèse.MoveFirst
synthèse = rs_SQL_synthèse("Localisation").Value
rs_SQL_synthèse.MoveNext
Do While Not rs_SQL_synthèse.EOF
synthèse = synthèse & rs_SQL_synthèse("Localisation").Value
rs_SQL_synthèse.MoveNext
Loop
DoCmd.RunSQL "delete * from new_test_Z2N_2"
Else
End If
Else
End If
DoCmd.RunSQL "UPDATE new_test_Z2N SET new_test_Z2N.Localisation = '" & synthèse & "' WHERE new_test_Z2N.[Code Défaut] = '" & code2 & "' "
rs_SQL_Code.MoveNext
Loop
Else
End If
Else
End If |
Partager