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
| Function SqlAnalytiqueUg() As String
Dim Chaine As String
Chaine = ""
Chaine = Chaine & "PARAMETERS VarUg Short, VarAn Short, VarMois Short;"
Chaine = Chaine & "SELECT DISTINCTROW "
Chaine = Chaine & "TITRE.IdTitre, "
Chaine = Chaine & "TITRE.Lib AS LibTitre, "
Chaine = Chaine & "ENV.IdEnv, ENV.Lib AS LibEnv, "
Chaine = Chaine & "UR.IdUr, UR.Lib AS LibUr, "
Chaine = Chaine & "Sum(DEPENSE.Montant) AS TotalMontant, "
Chaine = Chaine & "Sum(DEPENSE.MontantPrec) AS TotalMontantPrec, "
Chaine = Chaine & "Sum(CREDIT.Montant) AS TotalCredit "
Chaine = Chaine & "FROM "
Chaine = Chaine & "(TITRE INNER JOIN ENV ON TITRE.IdTitre = ENV.IdTitre) INNER JOIN (POLE INNER JOIN ((UG INNER JOIN ((UR INNER JOIN COMPTE ON UR.IdUr = COMPTE.IdUr) INNER JOIN CREDIT ON COMPTE.IdCpte = CREDIT.IdCpte) ON UG.IdUg = CREDIT.IdUg) INNER JOIN DEPENSE ON (UG.IdUg = DEPENSE.IdUg) AND (COMPTE.IdCpte = DEPENSE.IdCpte)) ON POLE.IdPole = UG.IdPole) ON ENV.IdEnv = UR.IdEnv "
Chaine = Chaine & "WHERE "
Chaine = Chaine & "UG.IdUg = [VarUg] "
Chaine = Chaine & "And DEPENSE.An = [VarAn] "
Chaine = Chaine & "And DEPENSE.Mois = [VarMois] "
Chaine = Chaine & "GROUP BY "
Chaine = Chaine & "TITRE.IdTitre, TITRE.Lib, ENV.IdEnv, ENV.Lib, UR.IdUr, UR.Lib;"
SqlAnalytiqueUg = Chaine
End Function
Private Function RequeteMdb()
Dim AppMdb As New DAO.DBEngine
Dim MdbData As Database
Dim sDir As String, sChaineSql As String
Dim Qry As QueryDef
sDir = ThisWorkbook.Path
' Requete Analytique Par Ug
MdbData.CreateQueryDef "QryAnalytiqueUg", SqlAnalytiqueUg()
MdbData.Close
End Function
Private Function ExecuteRequeteMdb()
Dim MdbData As Database, QryAnalyse As QueryDef, RsAnalyse As Recordset
Set MdbData = AppMdb.OpenDatabase(sDir & "\data.mdb")
Set QryAnalyse = MdbData.QueryDefs("QryAnalytiqueUg")
QryAnalyse.Parameters("VarUg") = sUg ' ----> Cells(1,2) par exemple
QryAnalyse.Parameters("VarAn") = CmbAn.Text
iMois = CmbMois.ListIndex + 1
QryAnalyse.Parameters("VarMois") = iMois
Set RsAnalyse = QryAnalyse.OpenRecordset
End function |
Partager