Leçon 28 : Requêtes : Les calculs

Calculs : Création d'une colonne de calcul, comparaison avec le paramètre dynamique

Il faut bien comprendre le mécanisme de calcul d'Access. Dans votre base de données habituelle ProFormation, créez une nouvelle requête basée sur T_Celebrite comme d'habitude. Placez-y les champs Prenom et NomClient. A doite de la colonne NomClient, ne mettez pas de champ, mais écrivez littéralement Tralala : . Appuyez sur ENTER, ou cliquez dans une autre case : Il écrit spontanément Expr1: [Tralala] : . Vous vous rappelez de la leçon précédente ? Nous avions également écrit Tralala, mais comme critère de PaysOrigine : . Cette fois, c'est comme en-tête de colonne que nous écrivons Tralala.

Lancez cette requête. Tout comme lors de la précédente leçon, Access nous demande ce que c'est que ce truc-là, Tralala ? . Répondez Pollux , et cliquez sur OK. Cette fois, vous obtenez ceci : Une nouvelle colonne titrée Expr1, qui contient Pollux pour chaque personne.

Il faut donc bien comprendre la façon de fonctionner de cette requête :

• Vous avez écrit juste Tralala comme en-tête de colonne
• Quand vous avez appuyé sur Enter, ou cliqué sur une autre case, ce tralala s'est transformé en Expr1:[Tralala]
• Quand vous avez lancé la requête, Access vous a demandé ce que c'était que Tralala
• Vous avez répondu Pollux
• Il a donné le résultat de la requête avec une colonne titrée Expr1, qui contenait systématiquement toujours Pollux

En fait :

• Tralala est une variable (une sorte de champ "calculé") qui va être rempli avec ce que vous allez lui préciser, et qui va bêtement répéter cette information sur toute la hauteur de la colonne
• Pollux est ce que vous allez mettre dans Tralala - En quelque sorte, Tralala est le "nom de la place de parking", et Pollux est la "marque de la voiture" que vous allez parquer dedans
• Expr1 est le titre de la colonne qui va contenir PolluxPolluxPolluxPollux etc. - Pourquoi est-ce que qu'Access ne met pas Tralala comme en-tête de colonne ? Simplement parce que vous pouvez très bien ne pas vouloir que Tralala soit comme en-tête de colonne, et nous allons voir qu'on peut remplacer Expr1 qui n'est pas terrible comme titre de colonne par un meilleur titre

Relisez bien cette première partie de leçon avant d'aller plus loin, parce qu il est très important de ne pas tout confondre.

Maintenant, essayons de remplacer Expr1 par Zébulon : . Essayez de deviner ce qui va se passer en lançant la requête avant de la lancer.

Alors si j'ai bien compris, il va encore me demander ce que c'est que Tralala, je répond cette fois par exemple "Pince à linge" et... Il va me faire une nouvelle colonne avec Pince à linge sur toute la hauteur de la colonne, mais cette colonne sera titrée Zébulon, et non plus Expr1 !

Parfaitement :

Mais alors, est-ce que je peux remplacer la "Pince à linge" de Carole Bouquet par, je ne sais pas moi... Par "Ecureuil" par exemple ?

Alors non. Absolument pas. Zébulon (je l'appelle zébulon parce que c'est le titre de la colonne, le nom du champ en quelque sorte) est un champ qu'on appelle un "champ calculé". Bon... il n'y a pas grand chose comme calcul, mais c'est considéré malgré tout comme un calcul par la requête.

Essayez : Remplacez Pince à linge de n'importe qui (Carole Bouquet si vous voulez) par Ecureuil : Vous ne pouvez pas : Il fait Bip, et surtout, dans la barre d'état (la barre qui est tout en dessous de votre fenêtre, (vous vous souvenez, quand on parlait de taille : "La taille doit être exprimée en centimètres", à la leçon 4). Eh bien, c'est dans cette même barre d'état que vous pouvez lire : . Le champ que vous avez appelé Zébulon, est basé sur une expression (un calcul en quelque sorte), et ne peut être modifié.

Eh oui : Si vous dites que Carole Bouquet par exemple a un "Zébulon" qui est "Ecureuil" à la place de "Pince à linge", où diable voudriez-vous qu'Access stocke cette information ???

Avec Excel, c'est différent, si vous écrivez une belle formule dans une cellule, et que vous arrivez ensuite avec vos gros sabots pour écrire quelque chose par dessus cette formule (l'écraser donc !), Pouf ! Fini la formule! Eh oui : Parce que là, Excel va simplement utiliser la cellule qui contenanit la formule pour stocker votre nouvelle information.

Ah ben on est chez Access ici ! la musique change ! Fini la belle vie avec Excel où tout le monde fait n'importe quoi ! (Entre nous, n'est pas plus confortable finalement ???)

C'est bien gentil tout ça, j'ai bien compris, mais a quoi est-ce utile ce "Zébulon" ?

Nous allons voir ça. Enregistrez votre requête sous R_CelebriteCalculZebulonTralala. Fermez-là, et donnez lui la description : Zébulon: [Tralala].

Créez une nouvelle requête comme d'habitude basée sur T_Celebrite avec les champs Prenom et NomClient. A côté de Nomclient, Ecrivez simplement la lettre s. Vous constatez qu'iul complète automatiquement par le champ salaireMensuel : . En fait, c'est simplement comme si vous aviez glissé le champ SalaireMensuel. Lancez la requête :

Par contre... Revenez en mode création, et effacez le champ SalaireMensuel, réécrivez Salaire mensuel, MAIS AVEC UNE FAUTE : SallaireMensuel (Avec 2 L à Salaire) : . Appuyez sur Enter, ou cliquez dans une autre case : Cette fois, nous nous retrouvons dans le cas de figure , c'est à dire qu'il écrit , il ne sait pas ce que c'est, lui, SallaireMensuel ! Lancez la requête : Et comme il fallait s'y attendre, il nous demande ce que c'est, SallaireMensuel. Répondez par exemple Tasse à café, et... Vous avez une colonne Expr1 qui contient sur toute sa hauteur Tasse à café, Tasse à café, tasse à café, etc.

Calculs : Création d'un calcul simple

OK : Si le champ est connu, on peut l'écrire directement au lieu de le glisser vers le bas depuis la liste des champs, mais gare aux fautes d'orthographe ! Comme d'habitude, quoi !

Eh oui. Bon, jusqu'ici, nous n'avons fait qu'observer au microscope les différents mécanismes de champ calculés des requêtes. Maintenant, nous allons passer aux cas pratiques et concevoir des choses bien plus intéressantes !

Fermez cette requête mais ne l'enregistrez pas : Ce n'est pas la peine, c'était juste pour vous montrer l'importance de l'orthographe !

Créez ensuite une nouvelle requête comme d'habitude, basée sur T_Celebrite, avec le prénom et le nom. Dans la 3ème colonne, nous allons calculer automatiquement le salaire, avec une prime de noël de 500 francs. Ecrivez . Appuyez sur Enter, ou cliquez dans une autre case, ça se transforme en Expr1: [Salairemensuel]+500. Lancez la requête. Et voilà : Le salaire avec une prime de 500 francs automatiquement calculée !

Super... Mais ce serait bien d'avoir également le salaire sans la prime quand même, pour pouvoir comparer les 2 colonnes...

Absolument. Faites glisser le champ SalaireMensuel sur Expr1: [Salairemensuel]+500 afin d'obtenir et relancez la requête :

Et si à la place de Expr1, je voulais que ce soit marqué "Salaire avec prime", j'écrirais : ?

Exactement. D'ailleurs, essayez ! Lancez ensuite la requête : . Enregistrez-là sous R_CelebriteCalculSalaireAvecPrime500

Formats des champs calculés dans les requêtes

C'est marrant que les 2 colonnes ne soient pas du même format : !

J'ai constaté ça : Je n'ai pas vraiment d'explication à donner. En fait, je m'en fiche un peu parce que pour tout vous dire, les requêtes ne sont pas une fin en soi : C'est un outil de calcul et d'extraction des données des tables, mais en fait, nous allons utiliser les formulaires et les états pour présenter les données de manières plus jolie que sur de simples colonnes comme nous le faisons, et, à ce moment-là, nous allons vraiment nous occuper des formats des différents champs, pour qu'ils soient affichés de manière tout à fait impeccables. Ici, que ce soit tel ou tel format, qu'il y ait le signe Euro, FF, $, ou n'importe quoi avant les chiffres, là n'est pas le problème.

Ceci dit, si en mode création vous cliquez avec le bouton droit de la souris sur le champ "Salaire avec prime", et que vous choisissez "Propriétés", vous pouvez choisir dans l'onglet Format : "Standard" : .

Calculs variables selon un paramètre

Et est-il possible d'attribuer une prime variable ?

Ah, si chaque personne a droit à une prime différente, là, nous ne pouvons plus faire de calcul : Il va vous falloir aller dans la table T_Celebrite, et y ajouter un champ Prime, et, pour chaque client, indiquer à quelle prime il a droit...

Oui, d'accord, mais ce n'était pas ça que je voulais demander : Je voulais que quand on lance la requête, il nous demande quelle prime on désire, et, pour chaque personne, il attribue cette même prime : Par exemple, si je dis 300, hop, il me donne tous les salaires ave 300 francs de prime. Je relance la requête une 2ème fois, cette fois, je dis 600, et zou ! il me renvoie tout le monde avec 600 francs de prime. C'est possible ?

Absolument, oui ! Nous allons mélanger les champs existants avec les nouveaux champs calculés. En mode création de votre requête, remplacez Salaire avec prime: [Salairemensuel]+500 par [Salairemensuel]+Prime. Appuyez sur Enter, ou cliquez dans une autre case, des crochets viennent s'installer automatiquement autour de Prime. Il y a donc 2 éléments dans ce calcul : SalaireMensuel, qui est déjà connu car c'est un champ de T_Celebrite... Et Prime qui est un truc complètement nouveau... Access ne sait pas ce que c'est. Alors, il va nous le demander. Lancez la requête : Comme prévu, il nous demande ce que c'est que cette bête bizarre : "Prime". Répondez 600, et cliquez sur OK. Et voilà:

Si vous voulez obtenir le salaire avec une autre prime, il suffit de relancer la requête une 2ème fois...

Faites Fichier/Enregistrer Sous R_CelebriteCalculPrimeVariable

Et si on voulait qu'il nous dise "Entrez la prime SVP" au lieu de tout sèchement "Prime", on pourrait écrire Salaire avec prime: [Salairemensuel]+ Entrez la prime SVP au lieu de Salaire avec prime: [Salairemensuel]+[Prime]

Exactement. Attention : Vous avez oublié les crochets autour de [Entrez la prime SVP].

Non, je m'en fiche puisque Access les rajoute automatiquement si besoin est !

Eh bien justement : Il rajoute les crochets lui-même uniquement s'il s'agit d'un titre de colonne qui ne contient pas d'espace(s) ! Essayez d'écrire Salaire avec prime: [Salairemensuel]+Entrez la prime SVP, et appuyez sur ENTER, ou cliquez dans une autre case : Vous obtenez cette erreur : . Cliquez sur OK (Pas le choix...). Ajoutez les crochets Salaire avec prime: [Salairemensuel]+[Entrez la prime SVP], et lancez la requête : Cette fois ça marche !

En résumé :

• Autour du texte, on met des guillemets : "Le texte"
• Autour des dates, on met des dièses : #01.01.1987#
• Autour des champs calculés ou des critères dynamiques, on met des crochets : [Entrez la prime SVP]

Souvent, Access rajoute lui-même ces encadrements, mais... parfois pas, comme dans l'exemple que nous venons de voir. C'est donc utile de connaître ces caractères d'encadrements.

Enregistrez les modifications de cette requête (CTRL-S si vous ne connaissiez pas le raccourci de Fichier/Enregistrer)

Calculs : Effectuer un calcul sur un champ déjà calculé

Pourrait-on effectuer des calculs sur d'autres calculs ? Par exemple, une fois qu'on a le salaire avec la prime, on calcule sur ce nouveau salaire les retenues sociales ?

Oui. Fermez votre requête, et créez une nouvelle requête comme d'habitude (T_Celebrite, prénom, nom, et SalaireMensuel)

Ecrivez Prime dans une nouvelle colonne: . Appuyez sur Enter. Il remplace Prime par Expr1:[Prime]. Si vous lancez la requête, il va vous demander la prime, vous répondez par exemple 500, et il vous donne le résultat avec comme titre de colonne Expr1, et 500 sur toute la hauteur. Bon, remplaçons Expr1 par Prime : , Comme ça, le titre de la colonne sera le même que ce qu'on demande : Prime ! Vous suivez ? On crée une confusion, mais pour que les choses soient plus claires aussi d'un autre côté...

C'est un peu confus !Access est d'accord avec vous : Il vous donne ce message d'erreur dès que vous lancez cette requête : . Vous ne pouvez pas écrire Prime:[Prime]... C'est comme ça ! Il faut donner un titre ou un calcul différent. Essayons ceci : Prime : [Entrez la prime] : . Cette fois ça marche.

Astuce : Si vous voulez un titre de colonne ressemblant le plus possible, ajoutez un trait de soulignement devant : _Prime : [Prime]

Ajoutez à la main encore dans la colonne plus à droite salairemensuel+prime : , qui se transforme automatiquement en Expr1:[SalaireMensuel]+[Prime].Remplacez Expr1 par SalaireAvecPrime (). Bon le SalaireMensuel, il connait, et la prime, il ne va la demander qu'une seule fois, et la réutiliser pour les 2 colonnes : Lancez la requête : . Voilà un exemple ou nous avons une colonne "calculée" (Prime), et un re-calcul dans une autre colonne de cette prime (SalaireAvecPrime).

Calculs : Calcul d'un pourcentage

Donc maintenant, sur cette nouvelle colonne SalaireAvecPrime, nous pouvons justement retirer les charges sociales. En Suisse, nous avons une retenue sur salaire qui s'appelle la retenue AVS/AI/APG (Assurance Vieillesse et Survivants / Assurance Invalididé / Assurance Perte de Gains) de 5.05% du salaire brut. C'est à dire que sur le total du salaire avec la prime, on va retirer le 5.05%.

Pour savoir comment faire, il faut réfléchir un peu, et imaginer comment on ferait sur une calculatrice qui ne serait pas pourvue du signe %. Alors ?

Hmmm... Je ferais le salaire fois 5.05%... Mais s'il n'y a pas le signe % je ferais donc le salaire fois 0.0505, alors...

Exactement. Par exemple, pour un salaire de 4'000 francs, ça ferait 4000* 0.0505, ce qui donne 202 francs. C'est le montant de la retenue. Alors donc, comment feriez-vous dans votre requête pour obtenir une colonne avec toutes les retenues sociales de toutes les célébrités ?

Eh bien, dans une nouvelle colonne, j'écrirais SalaireAvecPrime * 0.0505 ?

Oui. Vous n'avez pas besoin de mettre les crochets autour de SalaireAvecPrime car il n'y a pas d'espace (Access le fera à votre place), et vous ne pouvez pas écrire *5.05%, mais bien *0.0505 parce que les calculs dans les requêtes ne digèrent pas le signe pourcentage : , qu i va être transformé en . Vous commencez à avoir l'habitude : Remplacez Expr1 par RetenueSociale, ce qui va donner quand vous lancez la requête avec une prime par exemple de 250 francs : . Et voilà... Qu'est ce qui reste à faire maintenant ? Eh bien, il faut calculer le salaire net : C'est à dire le salaire avec la prime moins la retenue sociale. Vous devriez pouvoir faire ça tout seul, non ? Comment feriez-vous ?

J'écrirais dans une colonne encore plus à droite SalaireAvecPrime - RetenueSociale, tout simplement...

Mais oui... Et pour peaufiner, vous remplacerez Expr1 par SalaireNet (SalaireNet : [SalaireAvecPrime]-[RetenueSociale]), et finalement vous obtenez le résultat suivant (si la prime est de 120 francs):

C'est vraiment génial ! C'est juste dommage que les gens qui ont 0 francs de salaire ont également une prime... On devrait les exclure du résultat, non ?

Oui, ça vous pouvez le faire vous-même... Je vous laisse faire si vous voulez. Mais avant ça, sauvegardons notre précieuse requête : R_CelebriteCalculSalairePrimeRetenueSociale, et fermez-là. Mettez les descriptions suivantes à ces 3 dernières requêtes :

Bon... Hem... On peut résumer ?

Avez-vous bien compris ?

Exercice

Si vous n'êtes pas tout à fait certain d'avoir suivi correctement toutes les étapes de cette leçon, vous avez la possibilité de télécharger ici la version de ProFormation.mdb exactement dans l'état ou elle devrait être à la fin de cette leçon.

Avez-vous une question technique concernant cette leçon ? Cliquez ici !
Une remarque sur cette leçon ? Un problème ? Une erreur ? une ambiguité ? Soyez gentil de m'en informer