 |
 |
Leçon 12 |
 |
|
Les champs calculés
|
|||
Dans cette leçon, nous allons étudier un champ qui se calcule tout seul, apparu dans Access 2010 : le champ calculé (qu'on appelle Formule dans Excel, et Expression dans Access). Nous verrons comment fonctionne le générateur d'expression, et comment conjuguer les champs et les fonctions. Nos comparerons beaucoup Access et Excel sur le terrain des fonctions. Nous étudierons quelques fonctions de dates et de texte. Nous apprendrons également à "coller" plusieurs éléments côte à côte. Nous constaterons également que certaines fonctions sont impossibles à utiliser, absentes d'Access ou mal traduites de l'anglais. |
Sommaire
|
Commençons par un calcul simplissime. Créez un champ appelé AdditionSimple après Prenom, et définissez-le en Calculé :
Vous arrivez sur une boîte de dialogue appelée Générateur d'expression.
Ecrivez bêtement 1+1, et cliquez sur OK.
Constatez que votre calcul s'est reporté dans le paramètre Expression :
Lancez votre table en mode saisie de données.
AdditionSimple est un champ qui affiche simplement le résultat de votre calcul (2, donc). C'est donc un champ en lecture seule, et il est impossible d'y écrire quoi que ce soit.
Essayez d'inscrire, par exemple 45 dans AdditionSimple d'Edith Piaf, et vous verrez un message dans la barre d'état, comme ceci :
Avec Excel, si vous avez une colonne calculée et qu'il vous prenait l'envie d'écraser une formule par une valeur fixe, Excel se laisserait faire !
Dans cet exemple, nous avons des valeurs dans les colonnes E et F, et nous additionnons les valeurs dans la colonne G.
Si maintenant, je veux écrire 45 dans G2 à la place de =E2+F2, Excel va me laisser faire, et donc, vous aurez une addition correcte dans G1, le nombre 45 dans G2, et une addition correcte dans G3... Et si vous imprimez cette feuille de calcul, on ne verra nulle part qu'il y a ce "bug" 
.
Excel est tellement souple qu'il vous laisse assouvir tous vos caprices, même si c'est une erreur. Access vous cadre beaucoup plus ! Pas question d'avoir un seul de vos clients qui a autre chose que le résultat de 1+1 dans AdditionSimple !
Ce n'est évidemment d'aucun intérêt de créer un champ pour faire des calculs aussi triviaux que 1+1.
L'idée du champ calculé est de faire des opérations en rapport avec d'autres champs.
Nous allons calculer une prime en rapport avec le SalaireMensuel. Créez un nouveau champ SalaireAvecPrime sous Salairemensuel, en Calculé.
Dans le générateur d'expression :
Si vous cliquez trop de fois sur les champs dans Catégorie d'expression, il va vous afficher quelque chose de style :
Avec un <<Expr>> au milieu. Contentez -vous de sélectionner [SalaireMensuel] <<Expr>> [SalaireMensuel], de l'effacer avec la touche 
, et de recommencer.
Lancez la table en mode saisie de données, et constatez que les calculs sont corrects.
Le 2ème calcul est aussi correct, car le salaire est écrit en rouge, ce qui veut dire que c'est un nombre négatif (Nous avons vu ça dans les formats personnalisés, ici)
Le format n'est pas le même dans le champ SalaireMensuel (le format n'a pas été recopié - la preuve avec le 2ème salaire), mais Access a compris qu'il s'agissait d'un type de données monétaire.
Vous pouvez bien sûr changer le format dans les propriétés du champ. Par exemple, enlevez le CHF, en choisissant comme format : standard.
Si vous modifiez un SalaireMensuel, le calcul s'effectue automatiquement dès que vous enregistrez, ou que vous changez de champ ou d'enregistrement.
Remplacez CHF 1'235.00 par 4700 :
Vous pouvez modifier la formule directement dans la propriété Expression. Augmentez tous les SalaireMensuel de 5% par exemple.
On ne peut pas écrire [SalaireMensuel]*105%, ça ne marche pas !
Si vous cliquez sur les trois petits points tout à droite, vous retournez dans le Générateur d'expression :
Regardons plus attentivement ce que contient ce générateur d'expression (Une expression, c'est un calcul, tout simplement)
Selon ce qu'on choisit à gauche (Eléments d'expression), le générateur d'expression propose des choix dans la colonne du milieu (Catégories d'expressions) et/ou dans la colonne de droite (Valeurs d'expressions).
Intéressant :
T_Client : nous permet de choisir un champ de notre table, comme nous avons fait tout à l'heure.
Intéressant :
Fonctions : il faut cliquer sur le petit + à gauche, et cliquer sur Fonctions intégrées, et on obtient la liste de toutes les fonctions possibles (Elles ressemblent aux fonctions d'Excel, mais Access est moins fourni qu'Excel).
Quand on clique sur <Tout> au milieu, on a la liste de toutes les fonctions possibles sur la droite. Si on clique sur Date/Heure (par exemple) au milieu, on voit alors la liste de toutes les fonctions qui ont à voir avec les dates et les heures
Peu intéressant :
Constantes contient "", Faux, Null et Vrai... Aucun intérêt ! Oubliez ! ... Les mots clés Faux, Vrai, etc. sont intéressants, mais leur présence dans le générateur d'expression n'a pas d'intérêt.
Peu intéressant :
Opérateurs : +, -, *, /, <, > ... Pas besoin d'une liste pour ça... On a été assez grands tout à l'heure pour écrire +100 tous seuls ! Oubliez !
Regardons plus attentivement les fonctions
Nous allons préciser l'année de naissance de nos clients. Commencez par créer un nouveau champ AnneeNaissance en dessous de DateNaissance, en type de données Calculé.
Comme sur l'image :
Vous avez Année(<<date>>) qui s'affiche en haut.
<<date>> est le paramètre de la fonction, c'est à dire quelle date doit-il utiliser pour y afficher l'année ?
Voici comment choisir notre champ DateNaissance comme paramètre :
Exécutez la table en mode saisie de données :
Cette fonction est sympathique, mais elle n'est pas d'une utilité transcendentale... On voyait déjà bien les années dans la date de naissance...
Par contre, elle peut être utile pour connaître l'âge de nos clients ! A l'heure où j'écris ces lignes, nous sommes en 2012. J'ai donc 47 ans, car je suis né le 21.2.1965. Il suffit de calculer 2012 - 1965 = 47.
2012 moins l'année de la date de naissance du client donne donc... son âge (à une vache près).
Marche à suivre
Commencez par créer un champ calculé Age en dessous de DateNaissance.
Dans le générateur d'expression :
Et voilà les âges ! Evidemment, l'année prochaine, en 2013, ce champ calculé deviendra faux ! Il faudra penser à changer 2012 en 2013 ! C'est idiot !
Ce qui serait bien, ce serait de remplacer 2012 par une fonction qui nous renvoie la date d'aujourd'hui, dynamiquement.
Dans la leçon 8, quand nous parlions des dates, nous avons vu comment attribuer la valeur par défaut d'un champ Date/Heure à aujourd'hui grâce à la fonction Date(). Vous vous rappelez ?
Nous allons simplement remplacer 2012 par Année(Date()), comme ceci (Ecrivez-le à la main) : 
.
Au moment de cliquer sur OK, vous êtes gratifié d'un message d'erreur désagréable :
Access nous précise bien que la fonction Date() existe bien mais qu'on ne peut pas l'utiliser dans ce cadre ! C'est ballot, n'est-ce pas ?
Par contre, lorsque nous verrons les requêtes, il sera alors très possible de faire un calcul utilisant Date().
Voici le résultat d'une requête, juste pour vous montrer :
Pour illustrer mon propos, regardez ce tableau comparatif :
En haut, la liste de toutes les fonctions disponibles pour créer la valeur par défaut.
Elle contient beaucoup de fonctions (en vert) qui ne sont pas accessibles...
... à la contruction d'un champ calculé (par exemple, la fonction Date().
Ne me demandez pas pourquoi !
Et pour compléter le tableau, si vous avez besoin d'afficher la date du jour dans Excel, vous ne devez pas utiliser la fonction Date() mais la fonction Aujourdhui()
Et comme si ça ne suffisait pas, la fonction Date() existe aussi dans Excel, mais elle ne renvoie pas la date du jour, elle fait autre chose (mais on va s'arrêter là)
Ces petites mésaventures doivent vous permettre de prendre conscience de quelques faiblesses d'Access :
de traduction (Message d'erreur avec Year au lieu de Année)Je peux vous garantir que ces petites anicroches nous donnent parfois du sacré fil à retordre !
Mais d'une manière générale, rassurez-vous, la machinerie Access fonctionne plutôt bien ! (Sinon, je ne me donnerais pas tant de peine pour pondre ce cours
)
Voyons maintenant comment manipuler du texte. Vous aimeriez afficher les initiales de chaque client, comme ceci :
Il n'existe pas de fonction toute faite qui s'appellerait "Initiales". Nous allons devoir la construire, petit à petit.
Analysons l'exemple d'Edith Piaf :
Il y a donc 4 éléments que nous allons "coller" l'un à l'autre.
Comment colle-t-on des éléments ?
Avec le "Et commercial" : &. Sur les claviers suisses (QWERTZ), ce & se trouve au dessus du chiffre 6. Sur les claviers français (AZERTY), il se trouve sous le chiffre 1.
Faisons un premier test avec ce &. Créez un champ Initiale, sous Prenom, en type Calculé.
Voici le résultat.
Mais les deux champs sont tout collés ! On peut y ajouter un espace, ou du texte, ou un point, en le mettant entre guillemets.
Retournez dans l'expression de Initiale :
Corrigez à la main l'expression, comme ceci : 
, ça donne : 
Le texte se prénomme est collé au NomClient et au Prenom.
Les petits espaces que je vous ai demandé de mettre avant et après le & ne sont pas affichés : ils ne sont pas indispensables, mais j'ai constaté que c'était mieux de les mettre, sinon, Access renvoie parfois une erreur.
Le & fonctionne comme du ciment qui permet de "coller" des briques : une brique, c'est un [Champ entre crochets] ou "du texte entre guillemets".
ça commence toujours par une brique et ça se finit toujours par une brique (Jamais par &)
On a vite fait d'oublier un & entre deux éléments ! Il est très facile aussi d'oublier un guillemet " ou un crochet ! Soyez attentifs !
Dans notre cas, nous voulons juste un espace entre le nom et le prénom. Il suffit de mettre un espace entre 2 guillemets : " ".
Essayez ! Voici le résultat à obtenir :
Maintenant, voyons comment extraire la première lettre d'un texte (le caractère le plus à gauche, donc).
Retournez dans le générateur d'expression de Initiale.
Effacez [NomClient] & " " & [Prenom].
Cherchez la fonction Gauche, double-cliquez dessus.
Regardez en bas, il y a un texte d'aide : "Renvoie un variant de type Texte contenant le nombre indiqué de caractères d'une chaine en partant de la gauche"
Non, non, moi non plus je ne comprends pas ! Ne faites pas de complexe !
Non seulement, c'est incompréhensible, mais en plus, ça m'a l'air faux... Ca ne renvoie pas un nombre entier, mais une chaîne de caractères...
Tout ça pour dire que c'est une fonction qui affiche les X premiers caractères de gauche d'un texte !
Si vous cliquez sur 
, vous atterrirez dans l'aide d'Access de la fonction Gauche, mais vous ne serez pas plus avancé, à moins que vous soyez un informaticien très expérimenté.
La fonction Gauche possède 2 paramètres :
Cliquez une fois sur <<string>> (ça le sélectionne en bleu), et double-cliquez sur Prenom pour le mettre à sa place.
Il vous ajoute inutilement <<Expr>> à droite de [Prenom]. Sélectionnez-le, et effacez-le.
Cliquez sur <<length>>, et écrivez 1 à la place : 
. Voilà le résultat : 
.
Maintenant, il s'agit d'ajouter un point : Vous l'ajoutez à la main, comme ceci : 
.
Maintenant, on ajoute la lettre de gauche du nom de famille : Pensez-donc à écrire d'abord & : 
.
On va à nouveau chercher la fonction gauche, comme tout à l'heure, je pense que vous deviez y arriver sans souci :
Et on termine avec le point final :
Voici notre résultat, comme promis :
Essayez de comprendre précisément pourquoi les initiales de Chaplin et Coluche sont un simple C encadrés de 2 points.Nous n'allons évidemment pas passer en revue toutes les fonctions dans cette leçon, car il y en a beaucoup trop, et certaines d'entre elles sont vraiment très spécifiques, notamment les fonctions mathématiques, qui permettent de calculer des logarithmes, des racines carrées, etc.
Les fonctions financières requièrent de sérieuses connaissances bancaires, et ne sont pas non plus utilisées fréquemment. Rien que la lecture de l'explication de DDB permet de comprendre qu'il faut vraiment s'intéresser aux problèmes bancaires.
Vous conviendrez qu'on ne se sert pas tous les jours de ces fonctions .
Je ne résiste pas à vous montrer l'aide de la racine : Renvoie un double spécifiant la racine carrée d'un nombre, savoureux, non ?
Nous verrons d'autres types de fonctions lorsque nous en aurons besoin, au fur et à mesure du cours.
Je voudrais toutefois vous parler de la fonction VraiFaux qui permet d'obtenir des résultats intéressants.
Cette fonction permet d'afficher tel ou tel texte selon la valeur d'un champ. Dans notre exemple, nous allons afficher Riche si le SalaireAvecPrime (qui est un calcul basé sur salaireMensuel) est supérieur à 4000, et Pauvre si c'est inférieur ou égal à 4000.
Ce n'est pas un exemple des plus utiles, mais il va vous permettre de bien comprendre le fonctionnement.
En gros :
oui, je suis en Suisse ).Il y a 3 éléments :
Si vous disposez d'Access en Français, voici la bonne façon d'écrire cette fonction :
Et si vous êtes sur une version anglophone:
Constatez que ce sont des points-virgules (;) qui séparent les paramètres en français, et des virgules (,) dans le cas de versions anglaises, la plupart du temps.
Oui, il y a 2 i à iif (ne me demandez pas pourquoi)
Faisons-le ensemble
Créez un champ EtatRichesse après salaireAvecPrime, en type de données Calculé (On peut donc faire un calcul basé sur un autre calcul)
Vous pourriez écrire toute la fonction à la main, mais exploitons plutôt le générateur d'expression :
Lorsque vous cliquez sur OK, vous voyez enfin la fonction VraiFaux qui apparaît à la place de iif :
Le résultat fonctionne parfaitement : 
Maintenant, admettons que nous désirions une colonne qui affiche le montant des salaires, mais, s'îl s'agit de salaires supérieurs à 5'000 francs, il faut afficher CONFIDENTIEL,
il faudrait écrire ceci :
Or, si vous essayez, vous allez voir ce message d'erreur totalement bizarre
Ce message signifie qu'il est ennuyé parce qu'il sera amené à parfois écrire du texte (le mot "CONFIDENTIEL"), et parfois du chiffre (Le SalaireMensuel).
Normalement, on ne s'occupe pas de la propriété Type de résultat : dans les cas précédents, il comprenait bien qu'il s'agiissait d'un nombre (calcul de l'age) ou d'un texte (Gauche, Riche et pauvre).
Mais ici, il hésite ! Texte ou nombre monétaire ? ... Pourtant, on a bien vu que le type de données Texte permettait de stocker indifféremment des chiffres et des lettres, mais voilà, Môssieur Access a décidé que ce n'était pas possible !
Si nous avions utilisé une requête, comme je l'ai montré ici plus haut, nous n'aurions eu aucun problème.
Il faut donc que nous trouvions une astuce, un pis-aller, pour obtenir ce que nous voulons : il faudrait que le salaire soit, dans ce cas, "transformé" en texte !
Pour ce faire, nous allons utiliser la fonction Majuscule : on ne veut rien mettre en majuscule, on est d'accord, mais le simple fait d'utiliser une fonction de texte transforme, comme par magie, le chiffre en texte. Modifiez l'expression comme suit :
Attention à ne pas vous tromper avec le nombre et l'emplacement des [crochets] et des (parenthèses) :
VraiFaux([SalaireMensuel]>5000;"CONFIDENTIEL";Majuscule([SalaireMensuel])).
Et voilà : il n'y a plus de message d'erreur, et on obtient le résultat escompté.
Malheureusement, les montants s'affichent sans formatage, mais bon, c'est mieux que rien :
Comparons une fonction Access avec la fonction équivalente dans Excel.
Imaginons une table (T_TestMajuscule) avec des clients dont les noms et les prénoms ont été saisis de manière peu scrupuleuse : parfois les noms sont écrits en majuscules, parfois en minuscules, parfois il manque la majuscule de l'initiale...
Bref : nous voudrions une colonne qui nous affiche le prénom et le nom en MAJUSCULE.
Nous allons utiliser la fonction majuscule (dans Access et dans Excel. Comme paramètre de cette fonction, nous allons utiliser la concaténation (le collage avec &) du prénom et du nom, comme ceci : Majuscule(Prenom & NomClient).
N'oublions pas les [crochets] : Majuscule([Prenom] & [NomClient]).
Voici le tableau comparatif Access-Excel lors de la construction de la fonction
Remarquez que les fonctions Access et Excel ne sont pas exactement les mêmes : il y a bien majuscule, minuscule, NbCar (qui compte le nombre de caractères), La fonction NomPropre n'existe apparemment pas dans Access, Cette fonction permet d'imposer la première lettre de chaque mot en MAJUSCULE, et le reste en minuscule.
Pendant longtemps, j'ai regretté cet état de fait, jusqu'à ce que je découvre, un peu par hasard, qu'il existait une fonction équivalente dans Access, qui ne s'appelle pas du tout NomPropre, mais StrConv, qui s'utilise avec 2 paramètres : le premier est la chaîne de caractères à transformer, et le 2ème paramètre doit être 3 pour obtenir le résultat escompté. Ainsi :
StrConv("jean-BAPTISTE du commun", 3)
renvoie
Jean-Baptiste Du Commun
Dans cette leçon, nous avons appris à utiliser le générateur d'expression dans le cadre des champs calculés.
Nous verrons par la suite que ce même générateur d'expression peut s'utiliser dans les requêtes, les formulaires et les états.
Nous avons appris à écrire les expressions avec et sans le générateur d'expression. Nous avons étudié quelques fonctions de dates et de texte (Date(), Année, Gauche, Majuscule).
Nous avons appris que les champs s'écrivent entre [crochets] et les textes entre "guillemets". Nous avons appris à coller (concaténer) les éléments avec &. Nous avons également vu comment construire des fonctions plus complexes comme VraiFaux.
Nous avons mis en lumière quelques faiblesses et manquements d'Access (Fonctions impossibles à utiliser, absentes d'Access, mal traduites, VraiFaux problématique en cas de mélange texte/monétaire).
L'exercice consiste à créer une nouvelle base de données que vous nommerez ExerciceLecon012.accdb.
Vous y créerez une table T_Collaborateur (ce sont tous les salariés d'une société).
Vous y créerez les champs suivants :
Avec ces données :
| NomCol | Prenom | Departement | Salaire |
|---|---|---|---|
| Morat | Hervé | Achats | 1500 |
| Caillard | Julie | RH | 7800 |
| Munssen | Samuel | Marketing | 2100 |
| Zubriggen | Alicia | RH | 19800 |
L'exercice consiste à créer deux champs calculés :
Voici le résultat escompté :
