Leçon 28 : Requêtes : Les calculs

Pour suivre cette leçon, vous devez avoir suivi les leçons précédentes. Ou plus précisément, vous devez être en possession de la base de données procd.mdb telle qu'elle était à la fin de la leçon précédente. Si vous n'êtes pas certain de l'avoir, vous pouvez la télécharger ici

Résumé de la leçon précédente

La dernière fois, nous avons vu que les paramètres entre crochets [ et ] permettent de créer une seule requête qui renvoie une foule de résultats différents, puisque c'est celui qui exécute la requête qui doit, au dernier moment, préciser sa demande. Cette astuce permet d'éviter la création de dizaines ou même de centaines de requêtes différentes.

Aperçu de cette leçon

Dans cette leçon, nous allons à nouveau utiliser les crochets, mais cette fois pas pour rtourner dynamiquement tels ou tels enregistrements, mais pour effectuer des calculs : Dans Excel, si vous avez 45 dans la cellule A1, et que vous voulez calculer le double de cette cellule dans la cellule B5 par exemple, dans B5, vous écririez =A1*2. Mais il vous est peut-être déjà arrivé avec Excxel la malheureuse mésaventure qui consiste à écrire sans le faire exprès sur une cellule qui contient un calcul, et hop ! Plus de calcul ! Vous allez voir dans cette leçon qu'Access est nettement plus sécurisant !

Il faut bien comprendre le mécanisme de calcul d'Access. Dans votre base de données habituelle ProCD, 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.

Relisez bien cette première partie de leçon avant d'aller plus loin, parce que c'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.

Mmh... Alors si j'ai bien compris, il va encore me demander ce que c'est que Tralala, je répond cette fois 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 !

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é 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 Bouque par exemple a un Zébulon qui est Ecureuil à la place de Pince à linge, ou voudriez-vous donc 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), Pouf ! Fini la formule! Eh oui : Parce que là, Excel va simplement utiliser la cellule qui contenanit la formule pour stocker votre nouvelle information. Ici, la musique change !

Bon c'est bien gentil tout ça, j'ai bien compris, mais a quoi est-ce utile ?

Nous allons voir ça. Un peu de patience. 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.

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 faires des choses bien plus intéressantes ! Fermez cette requête, et ne la sauvegardez 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 :

. Sauvegardez-là sous R_CelebriteCalculSalaireAvecPrime500

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à, quand nous y serons, 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.

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 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 frandcs 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...

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 !

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)

On pourrait faire des calculs sur des 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 rséultat 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é...

Vous êtes d'accord ? 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.

Ajoutez à la main encore dans la colonne plus à droite salairemensuel+prime :

, qui se transforme autoamtiquement en Expr1:[SalaireMensuel]+[Prime].Remplacez Expr1 par SalaireAvecPrime (

). Bon le SalaireMesnuel, 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).

Donc maintenant, sur cette nouvelle colonne SalaireAvecPrime, nous pouvons justement retirer les charges sociales. En Suisse, nous av ons une retenue 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 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 socuiales 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 mai ntenant ? 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 ?

Eh bien 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 otenez 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

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 :

J'EN SUIS ICI

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 procd.mdb exactement dans l'état ou elle devrait être à la fin de cette leçon.