Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :
Sujets que vous pourriez aussi aimer :Réaliser des calculs de cumuls
Grâce aux
requêtes de synthèse,
Access sait parfaitement réaliser des recoupements sur lesquels il engage des opérations de consolidation. Mais ici, il est question de
cumul au coup par coup. Sur des heures travaillées au jour le jour, nous souhaitons comptabiliser progressivement le cumul de ces heures.
Source et procédure
Pour la démonstration de cette nouvelle
astuce Access, nous proposons de récupérer une petite
base qui offre un précieux module.
- Télécharger le fichier calcul-cumuls.rar en cliquant sur ce lien,
- Le décompresser dans le dossier de votre choix,
- Double cliquer sur le fichier résultant pour l'ouvrir dans Access,
- Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,
Comme l'indique le volet de navigation sur la gauche de l'écran, cette petite base est constituée d'une
table et d'un
module. Ce
module renferme une toute petite
fonction VBA mais d'utilité publique. Nous y reviendrons en temps voulu.
- Double cliquer sur la table heures pour l'afficher en mode Feuille de données,
Cette
table recense les heures effectuées par un employé sur des
dates précises.
Dans une colonne supplémentaire, nous souhaitons afficher sur chaque ligne, le cumul des heures travaillées avec le jour précédent. C'est donc nécessairement un
champ calculé qui doit intervenir dans une
requête. Ce
champ calculé doit exploiter la
fonction Access SomDom. Comme son nom l'indique, elle permet de réaliser les additions sur un domaine. Ce domaine est la
table heures. Mais cette somme doit être contenue. Elle ne doit pas être généralisée à l'ensemble de la
table. Elle doit progresser d'enregistrement en enregistrement pour offrir le
cumul progressif de jour en jour.
- Cliquer sur la croix de l'onglet pour fermer la table heures,
- Dans le volet de navigation, cliquer avec le bouton droit de la souris sur le module r_date,
- Dans le menu contextuel, choisir Mode création,
Nous basculons ainsi dans l'
éditeur de code Visual Basic Access. Et nous découvrons la
fonction DateUS selon la syntaxe suivante :
Function DateUS(ByVal dt As Variant)
If IsNull(dt) Then Exit Function
DateUS = '#' & Month(dt) & '/' & Day(dt) & '/' & Year(dt) & '#'
End Function
Le
champ calculé de la
requête à construire doit comparer les
dates entre elles pour offrir le cumul attendu. Les
dates Access s'écrivent différemment selon que vous travaillez dans l'interface graphique ou par lecode. Dans l'interface graphique, les
dates respectent les réglages régionaux, soit ceux calibrés sur le pays de la machine. Une date française s'écrira par exemple 14/07/2021, transformée en #14/07/2021#, pour les subtilités d'interprétation des données. Mais, dès lors que vous travaillez avec le
code Access, qu'il s'agisse de
VBA ou des
requêtes SQL, les dates sont régies par le système anglo-saxon. Les jours et les mois sont inversés. La même date devient donc #07/14/2021#. Et ne vous y trompez pas, dans l'
éditeur de requête, même en construction totalement manuelle, à l'issue c'est bien le
langage Sql qui prend le contrôle et commande l'exécution. C'est la raison pour laquelle nous avons besoin de cette fonction de conversion avant de pouvoir comparer les
dates entre elles, afin d'offrir le
cumul des heures travaillées.
Calcul du cumul des heures
Pour livrer le
cumul des heures travaillées, la
fonction Access SomDom est donc à l'honneur. Sa syntaxe est la suivante :
SomDom('[Champ]';'Table';'Critère'). En premier argument, nous devons lui indiquer le champ sur lequel réaliser les additions pour le
cumul. Il s'agit nécessairement du
champ h_duree de la
table heures. En deuxième argument, nous devons lui préciser dans quelle table se trouve ce champ. Il s'agit donc de la
table heures. Le dernier argument est celui de la
clause Where, soit celui de la contrainte. L'idée consiste à comparer la date en cours d'analyse avec toutes celles référencées dans le domaine, soit dans la table. Tant qu'elle est inférieure ou égale aux autres, la somme des durées doit s'effectuer. C'est ainsi que le cumul peut progresser au fil de l'évolution des dates analysées dans la
table.
- Fermer l'éditeur VBA Access en cliquant sur la croix de sa fenêtre,
- En haut de la fenêtre Access, cliquer sur l'onglet Créer pour activer son ruban,
- Dans la section Requêtes du ruban, cliquer sur le bouton Création de requête,
- Dans la boîte de dialogue qui suit, cliquer sur le bouton Ajouter puis sur le bouton Fermer,
Ainsi, nous intégrons l'unique table de cette base, la
table heures dans l'
éditeur de requête. Elle apparaît sous forme schématisée avec l'énumération de ses champs. Le symbole de l'astérisque (*) en entête les désigne tous.
- Double cliquer sur ce symbole de l'étoile pour intégrer tous les champs dans la conception,
- Elargir la colonne suivante de la grille de requête pour accueillir le champ calculé,
- Dans la zone champ de cette deuxième colonne, saisir le nom du champ : cumul:,
Celui-ci doit être suivi du symbole deux points. C'est ainsi dans la syntaxe Access que l'on déclare un
champ calculé.
- Ensuite, inscrire la fonction CDbl suivie d'une parenthèse, soit : CDbl(,
Le rôle de cette fonction est de convertir le résultat final en Réel double précision. De cette façon, la donnée résultante explicitement numérique sera naturellement alignée à droite. Il s'agit d'un artifice non négligeable en termes de présentation.
- Inscrire la fonction pour le calcul du cumul, suivie d'une parenthèse, soit : SomDom(,
- Désigner le champ de la durée entre guillemets et entre crochets, soit : '[h_duree]',
Comme vous le savez, tout champ s'exprime entre crochets dans la
syntaxe Access. Nous indiquons ainsi d'effectuer le
calcul du cumul fort naturellement sur le champ des heures travaillées.
- Taper un point-virgule (;) pour passer dans l'argument de la table hébergeant ce champ,
- Désigner la table par son nom entre guillemets, soit : 'heures',
- Taper un point-virgule (;) pour passer dans l'argument de la clause Where de la fonction,
C'est là que nous devons poser le critère consistant à vérifier que la date en cours d'analyse est bien inférieure aux autres. Tant que la condition est satisfaite, les heures des enregistrements correspondants doivent être additionnées au fil de la progression.
- Ouvrir les guillemets puis désigner le champ de la date, soit : '[h_date] ,
- Puis, inscrire le symbole inférieur suivi du symbole égal, soit : <=,
- Ensuite, fermer les guillemets,
Nous désignons ainsi la partie statique du critère. Les dates en cours d'analyse doivent être comparées à toutes celles de la table. Toutes les durées pour celles dont les dates sont inférieures aux autres doivent être cumulées. Pour cela, nous devons poursuivre la construction du critère en désignant le champ de la date dans une partie dynamique, agissant sur toute la colonne.
- Taper le symbole de concaténation (&),
- Puis, inscrire la fonction du module VBA suivie d'une parenthèse, soit : dateUS(,
- Désigner de nouveau le champ de la date entre crochets, soit : [h_date],
- Fermer la parenthèse de la fonction dateUS,
- Fermer la parenthèse de la fonction SomDom,
- Puis, fermer la parenthèse de la fonction CDbl,
- Enfin, valider la syntaxe à l'aide de la touche Entrée du clavier,
Le
champ calculé finalisé que nous avons construit est donc le suivant :
cumul:CDbl(SomDom('[h_duree]'; 'heures'; '[h_date]<='& dateUS([h_date])))
- Enregistrer la requête (CTRL + S) sous le nom r_cumul,
- Puis, cliquer sur le bouton Exécuter tout à fait à gauche dans le ruban Créer,
Comme vous pouvez le voir, nous avons parfaitement abouti le calcul du
cumul des heures travaillées qui grossit effectivement d'enregistrement en enregistrement, soit de date à date.