On sait que dans une base de données Microsoft Access, des champs calculés peuvent être créés dans une requête, un formulaire ou un rapport. Examinons la création de champs calculés dans un rapport de base de données Access 2003. Pour saisir des expressions dans un formulaire, vous devez l'ouvrir en mode Conception et créer un champ de texte libre à l'aide du bouton " un B" (Champ) sur la barre d'outils. À gauche du champ libre créé se trouve une étiquette associée au champ de texte, qui affiche le numéro de série du champ saisi.

Un champ libre signifie qu’il n’est associé à aucun champ. Dans le champ libre, vous pouvez saisir un champ de table de base de données ou une expression. Le libellé permet de saisir le nom du champ. Lorsque vous saisissez un champ d'expression libre qui calcule la valeur souhaitée, celle-ci devient une valeur calculée. Dans les champs calculés, vous pouvez utiliser des fonctions, telles que la fonction de date ou d'heure, ou vous pouvez saisir une expression qui utilise les valeurs des champs d'une table de base de données Access comme entrée. Vous pouvez également insérer une étiquette de texte autonome sur le formulaire, c'est-à-dire un libellé non associé à un champ de texte, pour cela vous devez utiliser le bouton " Aa" (Inscription) sur la barre d'outils.

Examinons la création d'un champ calculé dans le formulaire Étudiants en utilisant l'exemple de la base de données Training_students_VP. Énoncé du problème : dans le formulaire de la base de données Access Students_Constructor, créez un champ dans lequel doit être affichée la date du jour, et un autre champ qui combine le nom, le prénom et le patronyme de l'étudiant. Pour résoudre ce problème, ouvrez le formulaire Students_Designer en mode conception (Fig. 1).

Riz. 1.

Pour créer un champ calculé dans un formulaire de base de données Access qui affiche la date actuelle, vous devez effectuer les opérations suivantes :

• appuie sur le bouton " un B" (Champ) dans la barre d'outils, puis cliquez sur le formulaire à l'endroit où vous envisagez d'insérer un champ de texte libre ;
• placez ensuite le curseur dans le champ "Libre" (Fig. 2) et saisissez la fonction : =Date();
• l'étape suivante consiste à changer le nom de l'étiquette du champ de texte (Fig. 2), pour ce faire, vous devez supprimer le numéro de série (dans notre exemple Field30) et saisir le mot " Date de:";

Riz. 2.

• modifiez la longueur de l'étiquette et du champ de texte afin que le contenu y tienne ;
• formatez le champ calculé (si nécessaire), pour cela faites un clic droit sur le champ calculé et sélectionnez la commande Propriétés dans le menu contextuel ; la boîte de dialogue « Champ » s'ouvrira. Le formatage s'effectue dans la fenêtre « Champ » sur l'onglet « Tous » de la ligne « Format du champ » (Fig. 3) ;

Riz. 3.

• fermez la fenêtre Champ ;
• visualiser le formulaire de la base de données Access en mode formulaire en cliquant sur le bouton Ouvrir (Fig. 4) ;

Riz. 4.

Pour créer un autre champ calculé dans le formulaire de base de données Access qui affiche une expression qui utilise les valeurs des champs de table (Nom, Prénom, Patronyme) comme données d'entrée, vous devez effectuer les étapes suivantes :

• ajouter un champ libre au formulaire (Figure 5) ;

Riz. 5.

• changez le nom de l'étiquette du champ de texte en nom complet ;
• appelez le générateur d'expression, pour ce faire, cliquez sur l'icône « Builder » dans la barre d'outils, la fenêtre Builder s'ouvrira, dans laquelle vous devrez sélectionner « l'expression » (Figure 6) et cliquer sur OK ;

Dans cet article, nous parlerons des champs calculés dans les requêtes Access. Une requête, comme une table, peut effectuer des calculs sur des valeurs numériques, de chaîne ou de date pour chaque enregistrement en utilisant les données d'un ou plusieurs champs. Le résultat du calcul forme un nouveau champ calculé dans la table de requête. Contrairement aux champs de table calculés, les champs calculés des tables de base de données source ne créent pas de nouveaux champs. Chaque fois que la requête est exécutée, des calculs sont effectués sur la base des valeurs actuelles des champs.

Les expressions de champs calculés peuvent utiliser des constantes et des fonctions en plus des noms de champs. Suite au traitement d’une expression, une seule valeur peut être obtenue.

Tache 1. La table PRODUIT contient les champs PRIX et RATE_VAT. Calculez le prix TTC et comparez-le avec le prix obtenu dans le champ calculé de la table Prix TTC.

1. Créez une requête de sélection en mode conception pour la table PRODUCT. Faites glisser les champs NAME_TOV, PRICE, RATE_VAT et Price with TVA dans le formulaire de demande (Fig. 4.6).
2. Pour calculer le prix TTC, créez un champ calculé en écrivant l'expression [PRICE]+[PRICE]*[RATE_VAT] dans la cellule vide de la ligne Champ.
3. Pour sélectionner des enregistrements avec une valeur supérieure à 5 000 dans le champ calculé, saisissez > 5 000 dans la ligne Critères.
4. Après avoir saisi une expression, le système génère par défaut le nom du champ calculé Expression 1, qui devient l'en-tête de colonne dans le tableau contenant les résultats de la requête. Ce nom sera inséré avant l'expression [PRICE]+[PRICE]*[VAT_RATE]. Pour chaque nouveau champ calculé dans la requête, le numéro d'expression augmente de un. Le nom du champ calculé est séparé de l'expression par deux points. Pour modifier le nom, placez le curseur de la souris dans le champ calculé du formulaire de demande et cliquez sur le bouton droit de la souris. Dans le menu contextuel, sélectionnez Propriétés(Propriétés) et dans la ligne Légende, entrez un nouveau nom de champ - Prix ​​TTC1. Désormais dans le tableau des résultats de la requête, ce nom sera affiché dans l'en-tête de la colonne calculée. Le nom du champ peut également être corrigé directement dans le formulaire de demande.
5. Pour afficher le résultat de la requête, cliquez sur le bouton Exécuter(Courir) dans le groupe résultats(Résultats). Le champ calculé de la table et la requête ont les mêmes valeurs.
6. Modifiez le prix du produit dans l'un des enregistrements de la demande. Les valeurs des deux champs calculés seront instantanément recalculées.
7. Pour former une expression complexe dans un champ calculé ou une condition de sélection, il est conseillé d'utiliser le générateur d'expression. Le générateur vous permet de sélectionner les noms de champs nécessaires dans l'expression à partir de tables, requêtes, signes d'opération et fonctions. Supprimez l'expression dans le champ calculé et utilisez le générateur pour la générer.
8. Appelez le Générateur d'expressions en cliquant sur le bouton Constructeur(Constructeur) dans le groupe Configuration de la requête(Configuration de la requête) Conception du ruban, ou en sélectionnant Construire(Construire) dans le menu contextuel. Le curseur de la souris doit être préalablement positionné dans la cellule de saisie de l'expression.
9. Sur le côté gauche de la fenêtre Générateur d'expressions(Expression Builder) (Fig. 4.7) sélectionnez la table PRODUCT sur laquelle la requête est basée. Une liste de ses champs s'affichera à droite. Sélectionnez séquentiellement les champs et les opérateurs requis en double-cliquant dessus dans l'expression. L'expression sera formée en haut de la fenêtre. Veuillez noter que le constructeur a indiqué le nom de la table à laquelle il appartient avant le nom du champ et l'a séparé du nom du champ par un point d'exclamation.
10. Terminez le processus de construction d'une expression dans un champ calculé en cliquant sur le bouton OK.
11. Enregistrez la demande sous le nom ― Prix avec TVA et fermez-la.
12. Exécutez le fichier enregistré en le sélectionnant dans la zone de navigation et en sélectionnant Ouvrir dans le menu contextuel.

Tâche 2. Vous pouvez utiliser des fonctions intégrées dans les champs calculés et les conditions de filtrage. Access a plus de 150 fonctions définies.
Soit qu'il soit nécessaire de sélectionner toutes les factures pour lesquelles l'expédition a été effectuée au cours d'un mois donné. Dans INVOICE, la date d'expédition est stockée dans le champ DATE_OTG avec le type de données Date/Heure.

1. Créez une requête de sélection en mode conception pour la table INVOICE. Faites glisser les champs NOM_NAKL et CODE_SK dans le formulaire (Fig. 4.8).
2. Créer un champ calculé dans une cellule de ligne vide Champ(Champ) en y écrivant l'une des expressions : Format([INVOICE]![DISPOSITION_DATE];"mmmm") - cette fonction renverra le nom complet du mois
   ou Format([INBOOK]![DATE_DATE];"mm") - cette fonction renverra le numéro du mois.
3. Pour sélectionner les factures émises un mois donné, dans le champ calculé de la ligne Condition de sélection (Critères), saisissez le nom du mois, par exemple mars (Fig. 4.8), ou le numéro du mois, par exemple 3 conformément au paramètre dans la fonction Format.
4. Complétez votre demande en cliquant sur le bouton Exécuter(Courir) dans le groupe résultats(Résultats) sur l'onglet du ruban Travailler avec des requêtes | Constructeur(Outils de requête | Conception).
5. Écrivez la fonction Mois(INVOICE!DATE_OTG) dans le champ calculé et assurez-vous que cette fonction renvoie le numéro du mois extrait de la date.
6. Pour sélectionner toutes les lignes liées au deuxième trimestre, dans la ligne Critères, saisissez l'opérateur Entre 4 et 6, qui détermine si la valeur de l'expression se situe dans l'intervalle spécifié.
7. Écrivez l'expression MonthName(Month(INVOICE!DATE_OTG)) dans le champ calculé et assurez-vous que la fonction MonthName convertit le numéro du mois en son nom complet.

Pour renforcer cela, regardez le didacticiel vidéo.

Le champ calculé le plus simple est un champ qui doit afficher la date actuelle. Pour créer un tel champ :

1. Créer un contrôle de type gratuit Champ(Zone de texte) à l'endroit souhaité sur le formulaire.
2. Saisissez l'expression =Date() directement dans le champ. Le signe égal est obligatoire.
3. Ouvrez la fenêtre Propriétés(Propriétés) pour cet élément et définissez la propriété Format du champ(Format) format de date souhaité, par exemple Format de date longue(Date longue).
4. Définissez la longueur du champ afin qu'elle corresponde au nombre de caractères requis.
5. Modifiez le nom de l'étiquette de cette zone de texte. Le champ créé ressemblera à celui illustré sur la Fig. 9.47.

Riz. 9.47.

En règle générale, le contrôle que vous choisissez pour créer des champs calculés est Champ de texte, bien que ce ne soit pas nécessaire. Vous pouvez utiliser pour cela tous les contrôles possédant la propriété Données(Source de contrôle).

C'est dans la propriété Données(Control Source) doit recevoir une expression qui calcule la valeur de cet élément. Vous pouvez saisir une expression directement dans le champ, comme nous l'avons fait dans l'exemple. Cependant, si l’expression est assez longue, il n’est pas pratique de la saisir directement dans le champ. Vous pouvez saisir une expression dans une cellule de propriété Données(Source de contrôle) dans la fenêtre Propriétés(Propriétés), et si l'expression ne rentre pas dans la cellule de propriété, appuyez simplement sur la combinaison de touches +et ouvre la fenêtre Zone de saisie(Zoom). Vous pouvez également utiliser le générateur d'expressions.

Les champs calculés sont le plus souvent créés sous forme de tableau ou de ruban pour afficher une colonne avec certaines valeurs calculées. Par exemple, le coût d'un produit est calculé comme la valeur de l'expression Prix * Quantité. Pour afficher ces données calculées, deux méthodes sont utilisées :

• La première méthode repose sur le fait que le formulaire est construit à partir d'une requête incluant des colonnes calculées. Le formulaire crée un champ de texte qui spécifie une colonne de requête calculée comme source de données. Le champ est verrouillé afin que l'utilisateur ne puisse pas modifier les données qu'il contient, car ces données sont calculées lors de l'exécution de la requête.
• Dans le second cas, le formulaire peut être construit directement à partir du tableau. Il crée un champ de texte dans lequel une expression est spécifiée comme source de données, par exemple = [Prix unitaire] * Quantité. Dans ce cas, l'expression n'indique pas nécessairement les champs inclus dans le formulaire, mais tous les champs de la table de base.

La première méthode est préférable dans la plupart des cas, car une telle demande peut être utilisée non seulement sous une forme, mais également dans des rapports et autres formulaires. Un exemple d'une telle organisation des calculs est le formulaire « Sous-formulaire de commandes » de la base de données « Northwind ». Il s'appuie sur une requête « Order Details Extended » dans laquelle un champ calculé « ExtendedPrice » contenant une formule est créé.

Avant de pouvoir créer un champ calculé, vous devez ouvrir ou générer un tableau croisé dynamique. Notre exemple utilise le tableau croisé dynamique illustré à la Fig. 5.6.

Maintenant que nous disposons d'un tableau croisé dynamique, il est temps de créer un champ calculé. Pour créer un champ calculé, activez la boîte de dialogue Insérer un champ calculé.

Pour l'ouvrir, sur l'onglet contextuel du ruban Options, sélectionné dans le groupe d'onglets contextuels Outils de tableau croisé dynamique, allez dans le groupe Calculs, cliquez sur le bouton Champs, éléments et ensembles et sélectionnez la commande Champ calculé dans le menu déroulant, comme le montre la fig. 5.7.

La boîte de dialogue Insérer un champ de calcul apparaîtra à l'écran, comme le montre la Fig. 5.8.

En haut de la boîte de dialogue se trouvent deux champs de texte : Nom et Formule. Dans ces champs, vous devez préciser le nom du champ calculé et créer une formule en spécifiant les champs de données requis et les opérateurs mathématiques. En figue. La figure 5.9 montre que le champ calculé reçoit le nom descriptif Revenu moyen par heure. Ce nom doit décrire avec précision le type d’opération mathématique effectuée.

Ensuite, accédez à la liste Champs et double-cliquez sur le champ Volume des ventes. Tapez une barre oblique / pour indiquer à Excel que vous diviserez la valeur du champ Montant des ventes par un autre élément.

Par défaut, la zone de texte Formule de la boîte de dialogue Insérer un champ calculé contient l'expression = 0. Vous devez supprimer le zéro avant de saisir votre propre formule.

À partir de maintenant, la boîte de dialogue devrait ressembler à celle illustrée à la Fig. 5.10.

Riz. 5.10. Commencez à saisir la formule avec la valeur = « Volume des ventes »/

Terminez la saisie de la formule en double-cliquant sur le champ Période de vente (en heures). La formule terminée est présentée sur la Fig. 5.11.

Graphique 5.11. La formule complète = 'Volume des ventes' / 'Période de vente (en heures)' affiche le champ calculé requis

Cliquez sur le bouton Ajouter puis sur le bouton OK pour activer le nouveau champ calculé. Comme on peut le voir sur la Fig. 5.12, le résultat est un champ calculé à l'intérieur d'un tableau croisé dynamique.

Veuillez noter qu'après avoir créé un nouveau champ Revenu moyen par heure, il est ajouté à la liste des champs.

Le résultat obtenu à partir d'un champ calculé n'est pas formaté. Si vous le souhaitez, vous pouvez le formater à l'aide de la technique décrite dans Créer un tableau croisé dynamique de base.

Cela signifie-t-il que vous avez ajouté la colonne à la source de données ? Non. Les champs calculés sont similaires aux éléments Sous-total et Grand Total par défaut d'un tableau croisé dynamique, car ce sont toutes des fonctions mathématiques qui recalculent les données lorsque vous modifiez ou mettez à jour le tableau croisé dynamique. Les champs calculés imitent simplement les champs strictement définis dans la source de données. Vous pouvez les faire glisser, modifier les paramètres des champs et les utiliser avec d'autres champs calculés.

Regardez attentivement la fig. 5.11. Notez que le format de la formule que vous avez saisie est similaire à celui utilisé dans la barre de formule Excel standard. La différence évidente est qu'au lieu d'utiliser des références de cellules strictes ou des valeurs numériques, vous référencez les champs de données du tableau croisé dynamique pour définir les arguments utilisés dans les calculs. Si vous avez déjà travaillé avec des formules dans Excel, vous comprendrez rapidement les concepts de création de champs calculés.

Dans Microsoft SQL Server, vous pouvez utiliser des colonnes de valeurs calculées dans les tableaux, elles sont appelées "Colonnes calculées". Je vais maintenant vous expliquer pourquoi de telles colonnes sont nécessaires, quelles sont leurs fonctionnalités et, bien sûr, comment les créer.

Colonnes calculées dans les tableaux

Colonne calculée est une colonne de table virtuelle calculée en fonction d'une expression ; ces expressions peuvent impliquer d'autres colonnes de la même table. Ces colonnes ne sont pas physiquement stockées, leurs valeurs sont calculées à chaque accès. Il s'agit du comportement par défaut, mais vous pouvez stocker physiquement les colonnes calculées en spécifiant le mot clé PERSISTED lors de la création d'une telle colonne. Dans ce cas, les valeurs de cette colonne seront mises à jour lorsque des modifications seront apportées aux colonnes incluses dans l'expression calculée.

Des colonnes calculées sont nécessaires pour faciliter et rendre plus fiable l'obtention du résultat de certains calculs constants. Par exemple, lorsque vous accédez à une table, vous utilisez toujours une formule dans une requête SQL ( vous multipliez une colonne par une autre ou quelque chose comme ça, même si la formule peut être plus compliquée), ainsi, si vous définissez une colonne calculée dans une table en spécifiant la formule requise dans sa définition, vous n'aurez plus besoin d'écrire cette formule à chaque fois dans une requête SQL dans l'instruction SELECT. Il vous suffit d'accéder à une colonne spécifique ( colonne calculée), qui applique automatiquement cette formule lors de l'affichage des valeurs. De plus, cette colonne peut être utilisée dans les requêtes tout comme une colonne normale, par exemple dans les sections WHEHE ( à la condition) ou dans ORDER BY ( en tri).

Il est également important de comprendre qu'une colonne calculée ne peut pas être spécifiée comme colonne cible dans les instructions INSERT ou UPDATE.

Création d'une colonne calculée lors de la création d'une nouvelle table dans Microsoft SQL Server

Imaginons que nous devions créer un tableau dans lequel les marchandises seront stockées en indiquant leur quantité et leur prix, alors que nous voulons constamment savoir combien nous avons d'un produit particulier, c'est-à-dire nous devons multiplier la quantité par le prix. Pour éviter de faire cela à chaque fois dans une requête, on crée une colonne calculée, dans la définition de laquelle on indique la formule correspondante. Toujours dans cet exemple, nous spécifierons le mot-clé PERSISTED afin que notre colonne soit physiquement stockée et mise à jour, c'est-à-dire n'était recalculé que si les valeurs des colonnes quantité ou prix changeaient.

--Création d'une table avec une colonne calculée CREATE TABLE TestTable ( IDENTITY(1,1) NOT NULL, (100) NOT NULL, NULL, NULL, AS ( * ) PERSISTED --Calculated column) --Ajout de données à la table INSERT INTO TestTable VALUES ("Briefcase", 1, 500), ("Pencil", 5, 20), ("Notebook", 10, 50) --Sélection de données SELECT * FROM TestTable

Ajouter une colonne calculée à une table existante dans Microsoft SQL Server

Supposons maintenant qu’il soit également nécessaire de connaître le montant en tenant compte d’un certain coefficient statique (par exemple 1,7). Mais cela nous a été signalé après avoir créé le tableau, en d’autres termes, nous devons ajouter une colonne calculée au tableau existant.

Ajout d'une colonne calculée à un tableau ALTER TABLE TestTable ADD SummaALL AS ( * * 1.7); --Sélectionnez les données SELECT * FROM TestTable

L'ajout de colonnes calculées est également disponible dans l'environnement graphique. Pour cela, il vous suffit de rechercher la table souhaitée dans le navigateur d'objets et de faire un clic droit sur le conteneur " Colonnes" et sélectionnez " Créer une colonne", c'est à dire. tout est comme d'habitude.

Spécifiez ensuite le nom de la colonne et le type de données ( type de données temporaire), et de définir une colonne calculée, dans " Propriétés des colonnes"vous devez trouver la section" Spécification de colonne calculée" et sur le terrain " Formule» indiquer la formule appropriée.

Une colonne calculée est définie exactement de la même manière lors de la création d'une table à l'aide de Management Studio, c'est-à-dire la formule est simplement indiquée dans la section appropriée.

Supprimer une colonne calculée d'une table

Si vous n'avez plus besoin d'une colonne calculée, vous pouvez la supprimer ; cela se fait de la même manière que la suppression d'une colonne normale.

ALTER TABLE TestTable DROP COLUMN SummaALL;

Ceci conclut mon histoire sur les colonnes calculées, dans mon livre « The T-SQL Programmer's Way ». Tutoriel sur le langage Transact-SQL » Je parle en détail de toutes les capacités du langage T-SQL, bonne chance !