Discussion :

[zemysto]

Bonjour à tous.

J'ai cherché à bien comprendre la façon d'utiliser des fonctions dans les requêtes Access, mais je n'arrive pas encore à les faire fonctionner.

Voici mon problème :
J'ai un table très simple, avec 3 champs:
- La clé primaire
- Le champ "date", finalement aussi unique que la clé
- Le champ "cours", donnant le cours du produit au jour donné


Je veux pouvoir faire une requête qui affichera ces 3 champs ainsi qu'un 4ème champ "Moyenne10Jours", calculé comme suit:
Le 4ème champ est égal à la moyenne historique des 10 derniers jours.

Ce champ est donc la moyenne arithmétique des 10 enregistrements du champs "cours" qui précèdent l'enregistrement actuellement calculé, ie par exemple, l'enregistrement 23 de ce champ "Moyenne10Jours" est égal à la moyenne des "cours" des enregistrements 14 à 23, et ainsi de suite.



Ce calcul, évidemment très simple en Excel, me paraît plus délicat en Access.


D'après ce que j'ai lu sur le forum et sur les tutoriels, je pense qu'une des solutions simples est de passer par une fonction que j'appelle pour calculer le champ "Moyenne10Jours" dans ma requête Access.


La fonction serait donc une boucle de ce type, avec le nom du champ (tbl) et le numéro de l'enreregistrement actuel (i) en données d'entrée:


Public Function Moyenne(tbl As Variant, i as integer) As Double
Dim Var1
For j = -9 To 0
Var1 = Var1 + tbl( i + j) 'somme des valeurs
Next
Moyenne = Var1 / 10
End Function


Mon problème, c'est que je n'arrive pas à appeler correctement cette fonction dans ma requête.

- Je ne sais pas comment dire dans ma requête qui appelle la fonction que le "i" est le numéro d'enregistrement actuel

- J'ai le même problème pour demander de mettre le champ "cours" en tant qu'input de la fonction.


Merci d'avance pour votre aide.

Bien cordialement,

Laurent

[pgz]

Bonjour,

Le pb est celui des premiers enregistrements, pour lesquels les cours des 10 derniers jours ne sont pas disponibles. Cela peut conduire à écrire une fonction si tu ne veux rien aficher pour les 9 premiers enregistrements. Si tu n'es pas trop regardant là-dessus, tu peux faire assez simple:
tu ajoutes un champ en écrivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MM10 : DAvg("Cours";"MaTable";"MaDate > #" & Format([MaDate]-10;"mm/dd/yyyy) & "# AND MaDate <= #" & Format([MaDate];"mm/dd:yyyy") & "#"

J'ai appelé ton champ 'date' 'MaDate' parce que 'Date' est réservé.
Après correction des erreurs de syntaxe, cela devrait marcher.

Bon courage,

PGZ

[random]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT a.date, a.cours, Avg(b.cours) AS moyenne10
FROM matable AS a, matable AS b
WHERE( b.clef>=[a].[clef]-11
 And b.clef<[a].[clef])
or b.clef=1
GROUP BY a.date, a.cours
ORDER BY a.date;

[zemysto]

Edit : Merci beaucoup pour VOS réponses.

Pour le problème des 9 premiers enregistrements, c'est en effet quelque chose à traiter, sur lequel je me pencherai ensuite...
(A ce propos, est-il possible de mettre une valeur na() comme dans Excel, pour que les résultats des 9 premiers enregistrements ne gênent pas leur réutilisation par d'autres requêtes?)



Sinon, PGZ, ton code me semble très bien, mais mon Access 2003 ne le digère pas.
Outre la petite faute de frappe dans les dates à la fin (avec des "/" partout et pas de ":"), il semble y avoir autre chose.


Access me met le message d'erreur suivant:
"La syntaxe de l'expression entrée n'est pas correcte.
Vous avez peut-être entré un opérande sans opérateur."

Plus précisément, Access sélectionne la partie centrale suivante :
# AND MaDate <= #


Je débute dans la syntaxe Access, et je n'arrive donc à trouver où cela cloche...



Edit : Merci pour ta proposition différente, random, car je n'avais jamais testé de requête SQL.


Laurent

[pgz]

Re,

Je corrige une mon ânerie:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MM10 : DAvg("Cours";"MaTable";"MaDate > #" & Format([MaDate]-10;"mm/dd/yyyy") & "# AND MaDate <= #" & Format([MaDate];"mm/dd/yyyy") & "#"

MaDate c'est le nom de ton champ qui contient la date, et que tu ne dois pas appeler Date.

Si tu confirmes le pb des 9 premiers enregistrements, on arrangera cela après.

A+

PGZ

[zemysto]

Merci PGZ.

Oui, j'ai changé le nom "date" en "Madate", tout comme le nom de ma table en "matable" pour correspondre.

Mais j'ai toujours un bug Access, qui m'écrit:
"Il manque dans l'expression une parenthèse fermante, un crochet (]) ou une barre verticale (|)."

J'ai vérifié mon "copier-coller" en le refaisant plusieurs fois : il reste une erreur de syntaxe quelque part.

En fait, il manque une fermeture de parenthèse.
Mais quand je l'ajoute à la fin, la fonction marche (elle est convertie de l'anglais au français), mais les résultats de la requête affichent tous: #Erreur


Random, la fonction marche avec ta méthode, et je t'en remercie.
Cependant, je devrais faire autrement pour ne pas utiliser un des "regroupements" de Access avec l'opération simple "moyenne" pour faire des calculs plus complexes sur les "x derniers cours". (ex : statistiques de distribution, etc.)

Merci à tous les deux.


Laurent

[random]

oki


mais les fonctions de domaine proposent les mêmes fonctions que les fonctions de regroupement, et sont moins performantes que le sql

[zemysto]

oki


mais les fonctions de domaine proposent les mêmes fonctions que les fonctions de regroupement, et sont moins performantes que le sql

Ok.
Mais on peut bien ajouter ses propres fonctions aux fonctions "de domaine", ce qui permet d'élargir une formule du type de celle de Pgz à toutes les fonctions que l'on veut, non?


Si je ne me trompe pas, il reste donc à faire marcher la formule de Pgz (je ne trouve pas l'erreur...) et faire des fonctions "propriétaires" en plus, pour faire des calculs statistiques plus poussés sur le même genre d'éléments.

Non?

[pgz]

Re,

Mais j'ai toujours un bug Access, qui m'écrit:
"Il manque dans l'expression une parenthèse fermante, un crochet (]) ou une barre verticale (|)."

Je fais un essai chez moi et je t'envoie la formule corrigée.

A+

PGZ

[pgz]

Le pb venait du format pour lequel il faut dans ce cas utiliser la codification en français...

Le truc vérifié :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MM10 : VraiFaux(RechDom("MaDate";"MaTable")<[MaDate]-8;MoyDom("Cours";"MaTable";"Madate > #" & Format([MaDate]-10;"mm/jj/aaaa") & "# AND MaDate <= #" & Format([MaDate];"mm/jj/aaaa") & "#");"ND")

Du coup j'ai exclu l'affichage pour les premiers enregistrements.

Bon courage,

PGZ

[zemysto]

Merci beaucoup Pgz.

Ca fonctionne très bien.


Par ailleurs, j'ai compris l'utilisation du "vraifaux".
Moi qui avais peur que cette fonction "IF" n'existe pas dans Access sans passer par le IFF de la forme SQL, je suis rassuré.



Par contre, pourrais-tu me dire pourquoi on doit regarder le format de la date (et le tester en version américaine) avant de faire le calcul?
Je n'ai pas très bien compris ce point...


Merci beaucoup d'avoir pris le temps de m'aider à résoudre mon problème.

[pgz]

Par contre, pourrais-tu me dire pourquoi on doit regarder le format de la date (et le tester en version américaine) avant de faire le calcul?
Je n'ai pas très bien compris ce point...

Dans Access, quand on exprime une date en valeur, c'est toujours entre dièses(#) et en format américain.
Quand on utilise des variables, ça se débrouille tout seul.

Dans cette affaire si tu regardes l'affichage SQL de la requête, tu verras que les fonctions sont américaines. Par exemple le VRAIFAUX devient justement Iif, et l'instruction de formatage de la date s'écrit Format(..., "mm/dd/yyy").

Dans le QBE les instructions sont en français, mais il faut toujours passer la date exprimée en VALEUR dans le format américain. Mais comme on est dans le QBE il faut employer la codification en français, ... pour créer un format américain! D'où mon erreur initiale.

Avec tout cela je ne sais pas trop si j'ai éclairé ou obscurci ton point de vue!

Cordialement,

PGZ

[zemysto]

Merci pour ces éclaircissements Pgz (oui oui, tu as bien éclairé mon point de vue...)

Finalement, je crois que je dois en déduire que passer dès maintenant à une version US d'Access me serait préférable, pour avoir directement les "bonnes formules", sans ces fonctions à la française", qui n'ont en plus aucun lien avec les fonctions "à la française" d'Excel (le "si" devient "vraifaux", le "RechercheD" devient "RechDom", etc...)

Merci beaucoup.

Bien cordialement,

Laurent