Discussion :

[Piccou]

Bonjour à vous,

Je dois créer une fonction, qui devrait faire la chose suivante.

J'ai une grille de valeurs par périodes qui se présente sous la forme d'une requête (date, indices, valeurs ce ne sont pas exactement les mêmes nom)
A chaque enregistrement d'une période, comme il y a 180 indices, cela crée 180 valeurs par période. Ma problématique est qu'il faudrait que je fasse une moyenne de ces valeurs (j'en ai à ce jours un peu plus de 3000 dans ma table) par mois.
En fait si sur un mois de juin par exemple j'ai trois grilles, G1 qui courre du 24/05 au /18/06, G2 du 19/06 au 27/06 et G3 du 28/06 au 03/08 par exemple, il me faudrait une grille g= (((G1/ les 30 jours du mois de juin)*les 18 jours de présences de G1 dans juin) +((G2/ les 30 jours du mois de juin)*les 9 jours de présences de G2 au mois de juin)+((G3/les 30 jours du mois de juin)*les 3 jours de présences de G3 dans juin))/le nombres de grilles présentent dans juin ici 3.
Et ce n'est qu'un exemple mon module à pour but de faire cela de manière automatique pour chaque mois et il peut y avoir X grilles par mois comme 1. Sans compter qu'il y à des mois 30 jours 28 ou 29 ou encore 31 jours.
A ce moment du raisonnement je me rend compte que pour un novice du VBA que je suis (deux modules à mon actifs dont un avec aide sur ce forum), ça fait un beau troisième baptème du feux.

Alors je me demande déjà à quoi va ressembler la fonction, et aussi je ne sais pas si il faut compter en jours (faire du 1 au 31 du 32 au 59 ème jours etc etc) ou encore créer un calandrier jusqu'en 2000 et des brouettes
J'ai cru entendre qu'avec le VBA on avait des possibilité presque illimité...

Merci d'avance à vous

[petitbison]

bonjour,
mon vieux c'est bien balaise ce truc.
j'avais fais un truc pareil où il fallait pondérer les données sur périodes avec moyennes, écarts type, produits de sommes, sommes de produits et autre casses tête.
sache que les fonctions date de VBA reconnaissent les mois à 30 ou 31 jour, les années bissextiles et presque tout ce que tu peux imaginer.
Commence par poser proprement tes formules, agence tes données et ta démarche, tu passeras au code plus tard.
par la suite, si t'a un problème dans la traduction vers VBA tu trouveras plus facilement de l'aide.

A+

[Piccou]

Ouai c'est bien balaise comme truc, d'habitude même si la plus part des codes que je pose sont faux, du fait en grande partie que je suis un novice en VBA, d'habitude j'ai au moins une idée du code ^^

La je ne sais pas trop comment faire, et problème j'ai pas mais alors pas du tout, tout mon temps

J'avais pensé essayer de faire une requête avec un calandrier qui retracerai tout les jours, jusqu'en 2000 et des brouettes, et de le croiser avec ma requête des indices (qui à ce jours comporte un peux plus de 3000 lignes) pour avoir par jour la période d'indices qui correspond. Mais déjà cette requête la me pose problème. L'idée serait de mettre le calandrier et ensuite les période les indices et les valeurs, le problème et comment faire changer de période la requête. C'est à dire que si je fais une requête basic avec mes 4 champs, je em retrouve, à la date qui est égal à ma période tout les enregistrement de la période. Seulement je voudrai que pour chaque date dans la période donnée ça me mette la période avec les indices. Rien que ça c'est du lourd
En gros si je reprend mon exemple du début, une colonne date une colonne période, une indices et l'autre valeurs. J'aurai des lignes du 1 au 17 juin les valeurs de cette période, du 18 au 25 les valeurs de la deuxième etc.
Ce qui me donnerai une requête avec énormément de lignes puisque chaque période comporte 180 lignes et donc ça ferai 180 lignes en face de chaque date soit 65700 lignes par ans
Je pense que commencer par la serait un bon début non ?
En suite il faudrai lui faire une fonction qui demanderai pour chaque mois de faire la moyenne pondéré en fonction du nombre de période
Je fait appel à vous car moins je me trompe plus vite j'aurai finis ça qui est un "détail" dans mon appli si si .
Si quelqu'un à mieux ou la solution ou une idée ou un truc surtout ne pas hésiter, vos reflexion sont plus que bienvenus

Bonne journée à vous

[Piccou]

est ce qu'il existe un moyen pour que dans mon code je dise à access d'identifier le nombre de périodes présentes dans le mois ?

Que ça donne quelque chose comme:

trouver le nombre de période dans le mois, trouver combien de jours elles sont présentes dans le mois, faire la moyenne pondéré des périodes au prorata du nombre de jours présent puis, faire une moyenne des périodes avec les données précédente

mais ça m'a l'air costaud

[LE VIEUX]

Salut,
Tout est effectivement possible et pas forcement avec du code extra lourd :
Mais commençons par le commencement :
Tes tables – leurs structures – leurs relations
Comment sont-elles alimentées, saisie dans formulaire, import etc….
Les requêtes actuelles.
La simplicité de la requête finale dépendra d’une bonne structure de base.
Si tu peux mettre en post ta base avec un minimum d’enregistrements on pourras peut être t’aider plus facilement.

[petitbison]

bonjour,
le vieux a raison, à mon avis si tu donnes la table source de données avec quelques enregistrements (surtout le type des champs doivent correspondre à la réalité) et si tu donne aussi un exemple du résultat final (celui là tu peux le donner sous excel) il serait plus facile de t'aider.

A+

[Piccou]

j'ai pas ma base sous les yeux.
Mais demain dans la matinée je tenterai un poste complet sur ce sujet

à demain et bon week end les amis!!

[Piccou]

Bonjour à tous,

Alors je vais détailler mon application afin de vous permettre de mieux cerner le problème

Elle a pour but de calculer des valeurs à travers la saisie de données que l’utilisateur rentre.
Je n’était pas habitué à cela, mais ici la base fonctionne sans clé primaire et avec des relations basic
J’ai deux types de valeurs que l’utilisateur va saisir, les CTC et les CTL. Pour se faire je dispose des tables suivantes. VALEURS SCTL (Indicesmois ; Valeurs) DATE SCTL (Datectl) HISTO SCTL (Datectl ; Indicesmois ; Valeurs) et Date ctl (Datectl)
Pour les valeurs CTC ce sont exactement les même tables, il faut changer le TL par TC c’est tout.
Alors voilà, dans un formulaire l’utilisateur saisi la date qui s’enregistre dans DATE SCTL et des valeurs dans VALEURS SCTL. C’est deux tables se déverse dans HISTO SCTL et Datectl, puis avec une MAJ elles s’effacent puisque c’est le seul moyen que j’ai trouvé pour que le formulaire soit vierge à l’ouverture. Ensuite avec une requête les données sont interpolées. C'est-à-dire que j’ai des indices de 1 à 180 à la base mais qui courent à la saisie en 1 3 6 9 12 24 36 48 60 etc… Avec ma requête d’interpolation j’ai toutes mes valeurs pour chaque indice.
L’action est la même pour les CTC. Le problème arrive maintenant. Je dois faire la somme des CTC et CTL par mois (des données interpolées). Pour les CTC pas de problème ce sont des dates de fin de mois à chaque fois donc les périodes sont à chaque fois les mois en question. Pour les CTL problème les dates des périodes sont complètement aléatoires. J’entends par la que je peux avoir 3 dates par mois comme 1 dates pour 4 mois.
Et la je dois faire une moyenne pondéré des périodes pour avoir une grille de CTL par mois afin de pouvoir les additionner avec les CTC.

[Piccou]

exemple:

Dans ma table HISTO SCTL j'ai:

date Indicesmois Valeurs
26/03/2007 1 0.458
26/03/2007 3 0.876
26/03/2007 6 0.786

etc jusqu'à 180 ce la donne 17 valeurs car en indicesmois j'ai tjr 1 3 6 9 1 2 24 36 48 60 84 96 108 120 132 144 156 180

après interpolation j'ai les même valeurs mais éclaté:

Date
26/03/2007 1 0.458
26/03/2007 2 0.488

etc et de ce fait j'ai 180 valeurs par dates de saisie.

En conséquences dans ma requête d'interpolation ctl j'ai 3240 lignes et j'en ai 2700 pour l'interpolation ctc

L'objectifs est de faire une moyenne automatique par mois des grilles CTL pour avoir des grilles mensuel ctl
Cela doit être entièrement automatique l'utilisateurs ne doit rien faire, c'est une contraintes de plus puisqu'il faut dire à access:
1) d'identifier le nombre de grilles par mois
2) de faire la moyenne de ces grilles au prorata de leurs présence dans le mois
3) et de faire la moyenne des grilles une fois le prorata appliqué

voili voilou

[ilank]

Bonjour,

Si j'ai bien compris l'exposé du problème, le principe pour les CTC et CTL est le même, il s'agît de récupérer les enregistrements compris entre la dernière date inférieure ou égale au premier du mois à consulter et la dernière date inférieure au premier du mois suivant.

dans un premier temps essaie cette requête pour voir si tu récupères bien les enregistrements voulus. Le mois à consulter sera à saisir via une boîte de dialogue.

Code sql :

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

1
2
3
4
5
6
SELECT DateCTL,IndicesMois,Valeurs
FROM [HISTO CTL]
WHERE DateCTL
   BETWEEN (SELECT Last(DateCTL) FROM [Date CTL] WHERE DateCTL<=DateSerial(Year(Date()),mois,1))
 AND (SELECT Last(DateCTL) FROM [Date CTL] WHERE DateCTL<DateSerial(Year(Date()),mois+1,1))
ORDER BY DateCTL,IndicesMois

[Piccou]

alors j'ai bien une boite de dialogue ou je dois rentrer le mois. J'ai essayé janvier 2009 sous les formes suivante:
01/01/2009 01/2009 janvier 2009 et j'ai le même message d'erreur à chaque fois
"cette expression présente une syntaxe incorrecte, ou est trop complexe pour être évaluée. Par exemple une expression numérique peut contenir des éléments trop compliqués. Essayer de la simplifier en en affectant des parties à des variables"

Le but de la manoeuvre si je t'es compris c'est de récupéré les enregistrement d'un mois au hasard ?

il s'agît de récupérer les enregistrements compris entre la dernière date inférieure ou égale au premier du mois à consulter et la dernière date inférieure au premier du mois suivant.

Je vois pas ou tu veux en venir

Pour les CTC les données sont toutes donnée fin de mois donc il n'y a rien à faire dessus. Le but de la manoeuvre étant d'aditionner les ctc avec les ctl l'application fera automatiquement dans un future proche une adition des données ctc et ctl pour lesquels les mois sont égaux. Mais avant ça il faut que je lui fasse faire de manière automatique la moyenne pondéré par mois pour les ctl.
Je pourrai me taper tout les mois un à un avec une formule mais quand j'aurai finis l'appli et que quelqu'un fera un enregistrement il faudra que cette action se déroule toute seul

[ilank]

Bonjour,
il faut saisir le numéro du mois soit 1 pour janvier, 2 pour février...
Il s'agît de tester l'idée. Saisir un mois ce n'est plus le consulter au hasard puisqu'il faut le choisir.

[Piccou]

ok

alors j'ai un résultat. Mais quand je tape 1 j'ai le mois de janvier 2009 mais aussi le mois de novembre 2008 quand je tape 11 j'ai le mois d'aout 2008...

[ilank]

Ok, essaie en remplaçant Last() par Max().

[Piccou]

ok je vois pourquoi lorsque je tape 01 j'ai les valeurs de la saisie du 12/01/2009 et du 13/11/2008 parce que il n'y à pas de valeurs entre donc les valeurs du 13/11/2008 courent sur les 11 premiers jours du mois de janvier 2009

Donc maintenant on a une requête qui trouve les enregistrements présents sur un mois. Mais comment elle fait la reuqête pour saisir l'année de l'enregistrement ?

Donc au final si je ne dis pas de bêtises il faut créer une fonction qui ferai :

1) demander à Access de selectionner les dates qui courent sur un mois choisi on vien de le faire avec la requête sauf qu'avec la requête c'est nous qui choisissons le mois
2) compter le nombre ed dates différentes dans le mois
3) pour chaque grilles calculer le prorata des valeurs aux nombres de jours de présence dans le mois
4) faire la moyenne


ps: il faudrait peut être utiliser ma requête qui contient les données interpoler à la place de la table dans cette requête, ce serait mieu d'avoir toute les valeurs
la requête ce présente comme ça

interpolationctl:

champs Date ctl
Table croisement ctl(c'est une requête)

champs Indicesmois
table extension histo ctl (c'est une requête)

Champs Niveau: Mafonction()

[ilank]

Oui, mais mon idée est de procéder par étape pour avoir au moins le raisonnement à suivre puis tu pourras à adapter à tes besoins.
Pour le moment, on liste les enregistrements d'un mois donné.
1) Maintenant on va ajouter les dates de fin de chaque période d'un mois et le nombre de jours pour chacune de ces périodes.

Code sql :

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

1
2
3
4
5
SELECT [Début].DateCtl As [Début Période], [Début].IndicesMois, [Début].Valeurs, [Fin].DateCTL As [Fin Période], DateDiff("d",[Début Période],[Fin Période]) As [Nbr Jours]
FROM [Histo CTL] As [Début], [Histo CTL] As [Fin]
WHERE [Début].DateCTL BETWEEN (SELECT Max(DateCTL) FROM [Date CTL] WHERE DateCTL<=DateSerial(Year(Date()),mois,1)) AND (SELECT Max(DateCTL) FROM [Date CTL] WHERE DateCTL<DateSerial(Year(Date()),mois+1,1))
AND [Fin].DateCTL=(SELECT Min(DateCTL) FROM [Date CTL] WHERE DateCTL>[Début].DateCTL)
ORDER BY [Début].DateCTL;

Évidemment les dates et le nombre de jours peut ici ne pas correspondre au mois consulter; il faut limiter les champs de la requête [Début Période] et [Fin Période] au premier jour du mois et au dernier jour du mois de manière à disposer du bon nombre de jours pour chaque période.

Le champ [Début Période] devient

Code :

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

IIF([Début].DateCTL<DateSerial(Year(Date()),mois,1),DateSerial(Year(Date()),mois,1),[Début].DateCTL) As [Début Période]

Le champ [Fin Période] devient

Code :

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

IIF([Fin].DateCTL>DateSerial(Year(Date()),mois+1,1),DateSerial(Year(Date()),mois+1,1),[Fin].DateCTL) As [Fin Période]

[Piccou]

1) Maintenant on va ajouter les dates de fin de chaque période d'un mois et le nombre de jours pour chacune de ces périodes.

Donc je fais ça dans une nouvelle requête?

Le champ [Début Période] devient

Code :
IIF([Début].DateCTL<DateSerial(Year(Date()),mois,1),DateSerial(Year(Date()),mois,1),[Début].DateCTL) As [Début Période]Le champ [Fin Période] devient

Code :
IIF([Fin].DateCTL>DateSerial(Year(Date()),mois+1,1),DateSerial(Year(Date()),mois+1,1),[Fin].DateCTL) As [Fin Période]

ça je le met ou?

[random]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Function ponder(datedep As Date, datefin As Date, mois As Single, an As Single) As Double
Dim depart As Date
Dim fin As Date
depart = DateSerial(an, mois, 1)
fin = DateSerial(an, mois + 1, 0)
If datedep > fin Then
ponder = 0
Exit Function
End If
If datedep < depart Then datedep = depart
If datefin < depart Then
ponder = 0
Exit Function
End If
If datefin > fin Then datefin = fin
ponder = (datefin - datedep + 1) / (fin - depart + 1)
End Function

cette fonction va te renvoyer un coefficient à multiplier par ton montant

1 si les valeurs recouvrent le mois
0.5 pour le demi mois etc...


elle s'utilise dans une requete avec select ponder(les paramètres dans l'ordre)*montant


à voir la tete de ton pb on dirait un calcul de taux moyen de production

[ilank]

Donc je fais ça dans une nouvelle requête?

Non, la même pour le moment.

dans la requête tu remplaces

Code :

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

[Début].DateCtl AS [Début Période]

par

Code :

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

IIF([Début].DateCTL<DateSerial(Year(Date()),mois,1),DateSerial(Year(Date()),mois,1),[Début].DateCTL) As [Début Période]

et le champ

Code :

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

[Fin].DateCTL AS [Fin Période]

Par

Code :

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

IIF([Fin].DateCTL>DateSerial(Year(Date()),mois+1,1),DateSerial(Year(Date()),mois+1,1),[Fin].DateCTL) As [Fin Période]

Ainsi selon les données de ton premier exemple, pour le mois de juin la colonne [Nbr Jours] de cette requête doit renvoyer :
18, 9 et 3 soit 3 périodes pour le mois de juin une de 18 jours, une de 9 et la dernière de 3 jours. Maintentant tu disposes du nombre de jours de chaque période pour effectuer la moyenne sur tout le mois.

[Piccou]

Code :

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

1
2
3
4
5
SELECT IIF([Début].DateCTL<DateSerial(Year(Date()),mois,1),DateSerial(Year(Date()),mois,1),[Début].DateCTL) As [Début Période], [Début].IndicesMois, [Début].Valeurs, IIF([Fin].DateCTL>DateSerial(Year(Date()),mois+1,1),DateSerial(Year(Date()),mois+1,1),[Fin].DateCTL) As [Fin Période]
FROM [Histo SCTL] AS [Début], [Histo SCTL] AS [Fin]
WHERE [Début].DateCTL BETWEEN (SELECT Max(DateCTL) FROM [Date CTL] WHERE DateCTL<=DateSerial(Year(Date()),mois,1)) AND (SELECT Max(DateCTL) FROM [Date CTL] WHERE DateCTL<DateSerial(Year(Date()),mois+1,1))
AND [Fin].DateCTL=(SELECT Min(DateCTL) FROM [Date CTL] WHERE DateCTL>[Début].DateCTL)
ORDER BY [Début].DateCTL;

C'était un exemple pour le mois de juin les vrai périodes sont 05/05/2009 et 02/06/2009 sur ce mois
Donc elle (la requête ci dessus) me met bien 1 jours pour la première période et 29 jours pour la seconde. J'ai pas juste les deux grilles en beaucoup d'exemplaire puisque je pense que le plus logique serait que j'ai la grille pour le 1 juin et la grille pour les 29 jours suivant. Mais la je me retrouve avec 722 lignes... j'ai remarqué q'elle ne me met pas tout les enregistrements de la période. Par exemple pour juillet j'ai le 02/06/2009 le 07/07/2009 et le 29/07/2009 elle me sort les deux premier et prend le troisième pour la fin de période. Aussi elle m'affiche parfois 17 fois un même enregistrement ou plus ou moins ça dépend. Et à chaque fois c'est 722 lignes qui ressortent.
De plus pourquoi on utilise pas à la place ma requête d'interpolation parce que je vais devoir y venir. Elle s'appel interpolation ctl et les champs sont Datectl, Indicesmois, Niveau. J'ai essayé sur le premier code de le modifier pour incorporer cette requête à la place de la table mais Microsoft jet me dis que 'mois' n'est pas un nom de champs ou expression correct.


Random ce n'est pas un calcul de CMP, j'ai mis ma requête avec toutes mes données interpolées Datectl Indicesmois Niveau, j'ai placé les trois champs dans la requête et dans le quatrième j'ai mis t'as fonction préalablement copier dans un modul, et ACCESS me renvois que le moteur de base de donnée microsoft jet ne reconnait pas [datedep] en tant que nom de champs ou expression correct.