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
|
Public Sub requete_cours(mabdd, debut, debut2, fin, resultat)
Dim rgeneral As String
'MsgBox debut : 11/11/2008
'MsgBox debut2 : 11/12/2008
'MsgBox fin : 11/11/2009
rgeneral =
"SELECT Nom, Cours.CodeISIN, Cours.Cours_Cloture, [CoursMoyen].Moyenne
FROM (SELECT Cac40.Nom, Cac40.Code_ISIN AS CodeISIN, Cours_Cloture
FROM CAC40 INNER JOIN COTATIONS ON CAC40.Code_ISIN=Cotations.Code_ISIN
WHERE (Date='""#" & debut & "#""') ORDER BY Cac40.Code_ISIN) AS COURS INNER JOIN (SELECT Code_ISIN, AVG(Cours_Cloture) AS Moyenne
FROM Cotations
WHERE (Date BETWEEN '""#" & debut2 & "#""' AND '""#" & fin & "#""' )
GROUP BY COTATIONS.CODE_ISIN ORDER BY COTATIONS.CODE_ISIN) AS CoursMoyen ON Cours.CodeISIN=[CoursMoyen].Code_ISIN
ORDER BY Cours.CodeISIN;"
'Ancienne version de la requête
'rgeneral = "SELECT Nom, Cours.CodeISIN, Cours.Cours_Cloture, [CoursMoyen].Moyenne FROM (SELECT Cac40.Nom, Cac40.Code_ISIN AS CodeISIN, Cours_Cloture FROM CAC40 INNER JOIN COTATIONS ON CAC40.Code_ISIN=Cotations.Code_ISIN
'WHERE (Date=#7/10/2007#) ORDER BY Cac40.Code_ISIN) AS COURS INNER JOIN (SELECT Code_ISIN, AVG(Cours_Cloture) AS Moyenne FROM Cotations
'WHERE (Date BETWEEN #7/11/2007# AND #7/10/2008# )
'GROUP BY COTATIONS.CODE_ISIN ORDER BY COTATIONS.CODE_ISIN) AS CoursMoyen ON Cours.CodeISIN=[CoursMoyen].Code_ISIN
'ORDER BY Cours.CodeISIN;"
'"#" & debut & "#"'
Set vcours = mabdd.CreateQueryDef("", rgeneral)
Set resultat = vcours.OpenRecordset
End Sub |
Partager