Fonctions de domaine vs sous-requêtes

Objectif : apprendre à utiliser les fonctions de domaine comme les sous-requêtes.

Niveau requis : confirmé.

Commentez cet article : 6 commentaires Donner une note  l'article (5)

Article lu   fois.

L'auteur

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Les fonctions de domaine, à l'instar des sous-requêtes, peuvent être utilisées directement dans les requêtes pour effectuer par exemple des sous-totaux, des décomptes ou pour récupérer des valeurs selon certains critères.

Elles sont plus simples à mettre en œuvre que les sous-requêtes, mais sur le papier moins rapides, car, tout comme le SQL exécuté via VBA, elles ne bénéficient pas de l'optimisation que procure le moteur de données aux requêtes. Elles ont aussi l'avantage par rapport aux sous-requêtes de ne pas empêcher l'ajout ou la modification des données dans la requête principale.

Pour mieux comprendre leur mise en œuvre, voici des exemples concrets de leur utilisation :

  • numérotation des enregistrements ;
  • cumul de montants ;
  • récupération du montant du mois précédent.

Bien entendu, les requêtes présentées par la suite serviront de source de données pour des formulaires ou des états. Dans ce contexte, il est très recommandé en termes de performances d'utiliser les fonctions de domaine en amont, dans des expressions de requête, plutôt que directement dans des contrôles calculés de formulaire.

II. Exemples d'utilisation

II-A. Numérotation des enregistrements

II-A-1. Fonction de domaine CpteDom

Extrait de l'aide :

La fonction CpteDom (DCount) comptabilise les valeurs uniques dans un jeu d’enregistrements :
Synthaxe : CpteDom(expr, domaine [, critères]) ;
expr : partie obligatoire qui identifie le champ à utiliser pour comptabiliser les enregistrements ;
domaine : partie obligatoire qui définit le jeu d’enregistrements, à savoir un nom de table ou de requête ;
critères : cette étape est facultative et est une expression de chaîne qui limite la plage de données pour la fonction CpteDom.
Il est similaire à la clause WHERE dans une expression SQL

Mise en œuvre - numérotation des paiements :

On part d'une table T_Paiement pour enregistrer les paiements :

Champ

Type de données

Description

IdPaiement

Numéro-auto

Numéro d'enregistrement

IdFacture

Entier long

Identifiant de la facture reliée au paiement

DatePaiement

Date/Heure

Date du paiement

ModePaiement

Texte

Mode de paiement : "CB" ; "Cash" ; "Virement"

MontantPaiement

Monétaire

Montant du paiement

Pensez à bien indexer vos champs de recherche pour optimiser l'exécution de vos requêtes.

Puis, on construit une requête basée sur cette table :

Code SQL
Sélectionnez
SELECT IdFacture, IdPaiement, DCount("*","T_Paiement","IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement]) AS NumPaiement, DatePaiement, MontantPaiement, ModePaiement
FROM T_Paiement
ORDER BY IdFacture, IdPaiement;

Pour optimiser l'exécution de la requête, évitez si possible de mettre un ordre de tri ou une condition sur une expression qui contient une fonction de domaine.

Formule pour la numérotation (1) :

NumPaiement: CpteDom("*";"T_Paiement";"IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement])

Pour chaque ligne de paiement de la table, la fonction compte les paiements liés à la même facture et dont l'identifiant est inférieur ou égal à celui de cette ligne.

Formule équivalente :

NumPaiement: nz(CpteDom("*";"T_Paiement";"IdFacture=" & [IdFacture] & " and IdPaiement<" & [IdPaiement]);0) + 1

On peut aussi retrouver la fonction intégrée CpteDom à l'aide du générateur d'expression dans la catégorie regroup. domaine disponible en mode création de la requête depuis le bouton Générateur situé sur le ruban :

Générateur d'expression
Générateur d'expression

Si le critère avait porté sur un champ date, comme DatePaiement, il aurait fallu encadrer la valeur de date avec des signes dièse (# #), et la spécifier au format mm/jj/aaaa :

NumPaiement: CpteDom("*";"T_Paiement";"IdFacture=" & [IdFacture] & " and DatePaiement<=#" & Format([DatePaiement];"mm/jj/aaaa") & "#" )

Pour un champ de type texte, vous devez placer la valeur de texte entre guillemets (" ").
Si vous devez inclure un guillemet comme l’un des caractères dans la chaîne du critère, vous utilisez deux guillemets contigus ("").
En supposant que le champ IdPaiement est de type texte, on obtiendrait donc :

NumPaiement: CpteDom("*";"T_Paiement";"IdFacture=" & [IdFacture] & " and IdPaiement<=""" & [IdPaiement] & """")

Résultat de la requête :

Affichage des données
Affichage des données

Comme on peut le constater sur l'image, les données sont modifiables.

II-A-2. Sous-requête avec count

select count(expr) from domaine where Critères

Le résultat comporte une seule ligne et une seule colonne, il est dit scalaire.

Mise en œuvre - numérotation de paiements dans une requête :

Code SQL
Sélectionnez
SELECT IdFacture, IdPaiement, (select count(*) from T_Paiement As T1 where (T1.[IdFacture]=T_Paiement.[IdFacture]) and (T1.[IdPaiement] <= T_Paiement.[IdPaiement])) AS NumPaiement, DatePaiement, MontantPaiement, ModePaiement
FROM T_Paiement
ORDER BY IdFacture, IdPaiement;

Pour ne pas trop ralentir l'exécution de la requête, évitez de mettre une condition sur une expression de requête. À noter qu'Access ne permet pas de mettre un ordre de tri sur ce type d'expression.

Sous-requête pour la numérotation :

(select count(*) from T_Paiement As T1 where (T1.[IdFacture] = T_Paiement.[IdFacture]) and T1.[IdPaiement] <= T_Paiement.[IdPaiement])

Pour chaque ligne de paiement de la table, la sous-requête compte les paiements liés à la même facture et dont l'identifiant est inférieur ou égal à celui de cette ligne, suivant la condition :

(T1.[IdFacture] = T_Paiement.[IdFacture]) and T1.[IdPaiement] <= T_Paiement.[IdPaiement])

T1 est un alias pour T_Paiement et désigne la table dans la sous-requête, T_Paiement désigne la table dans la requête principale, cela permet de comparer les enregistrements de la sous-requête avec ceux de la requête principale.

Résultat obtenu :

Affichage des données
Affichage des données

Les données sont seulement en lecture. Si vous tentez de modifier les données, un message « Impossible de mettre à jour le Recordset » s'affiche dans la barre d'état.

II-B. Cumul des montants

II-B-1. Fonction de domaine SomDom

Extrait de l'aide :

Vous pouvez utiliser la fonction [B]SomDom[/B] (DSum) pour calculer la somme d’un ensemble de valeurs dans un jeu d’enregistrements spécifié (un domaine).
Syntaxe : SomDom(Expr, domaine, critères)

Mise en œuvre - calcul des cumuls des paiements dans une requête :

Code SQL
Sélectionnez
SELECT IdFacture, IdPaiement, DCount("*","T_Paiement","IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement]) AS NumPaiement, DatePaiement, MontantPaiement, ModePaiement, CCur(DSum("MontantPaiement","T_Paiement","IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement])) AS CumulPaiement
FROM T_Paiement
ORDER BY IdFacture, IdPaiement;

Formule pour le calcul des cumuls (2) :

CumulPaiement: SomDom("MontantPaiement";"T_Paiement";"IdFacture=" & [IdFacture] & " and IdPaiement <=" & [IdPaiement])

Pour chaque ligne de paiement de la table, la fonction somme les paiements liés à la même facture et dont l'identifiant est inférieur ou égal à celui de cette ligne.

Résultats de la requête :

Affichage des données
Affichage des données

On peut ajouter ou modifier des données.

II-B-2. Sous-requête avec sum

select sum(expr) from domaine where Critères

Le résultat comporte là aussi une seule ligne et une seule colonne.

Mise en œuvre - calcul des cumuls des paiements :

Code SQL
Sélectionnez
SELECT IdFacture, IdPaiement, (select count(*) from T_Paiement As T1 where (T1.[IdFacture]=T_Paiement.[IdFacture]) and (T1.[IdPaiement] <= T_Paiement.[IdPaiement])) AS NumPaiement, DatePaiement, MontantPaiement, ModePaiement, CCur((select Sum(MontantPaiement) from T_Paiement As T1 where (T1.[IdFacture]=T_Paiement.[IdFacture]) and (T1.[IdPaiement] <= T_Paiement.[IdPaiement]))) AS CumulPaiement
FROM T_Paiement
ORDER BY IdFacture, IdPaiement;

Sous-requête pour le calcul des cumuls :

 
Sélectionnez
(select Sum(MontantPaiement) from T_Paiement As T1 where (T1.[IdFacture]=T_Paiement.[IdFacture]) and (T1.IdPaiement <= T_Paiement.[IdPaiement]))

Pour chaque ligne de paiement de la table, la sous-requête somme les paiements liés à la même facture et dont l'identifiant est inférieur ou égal à celui de cette ligne, suivant la condition :

(T1.[IdFacture] = T_Paiement.[IdFacture]) and T1.[IdPaiement] <= T_Paiement.[IdPaiement])

T1 est un alias pour T_Paiement et désigne la table dans la sous-requête, T_Paiement désigne la table dans la requête principale, cela permet de comparer les enregistrements de la sous-requête avec ceux de la requête principale.

Résultats de la requête :

Affichage des données
Affichage des données

Les données sont en lecture.

II-C. Récupération du montant du mois précédent

II-C-1. Fonction de domaine RechDom

Extrait de l'aide :

La fonction RechDom (DLookup) recherche la valeur d’un champ particulier qui fait partie d’un jeu d’enregistrements défini (un domaine).
Syntaxe : RechDom (Expr, domaine, critères)

Mise en œuvre - comparaison des ventes mensuelles

On dispose d'une requête R_Ventes réalisant la somme des paiements par mois :

Code SQL
Sélectionnez
SELECT Format([DatePaiement],"mmmm yyyy") AS MoisVentes, Sum(CCur([MontantPaiement])) AS MontantVentes, Year([DatePaiement]) AS AnneeVt, Month([DatePaiement]) AS MoisVt
FROM T_Paiement
GROUP BY Format([DatePaiement],"mmmm yyyy"), Year([DatePaiement]), Month([DatePaiement])
ORDER BY Year([DatePaiement]), Month([DatePaiement]);

Requête pour comparer les ventes mensuelles :

Code SQL
Sélectionnez
SELECT MoisVentes, MontantVentes, Format(DLookUp("MontantVentes","R_Ventes",IIf([MoisVt]>1,"MoisVt=" & ([MoisVt]-1) & " and AnneeVt=" & [AnneeVt],"([MoisVt]=12) and AnneeVt=" & ([AnneeVt]-1))),"Currency") AS MontantPrecedent, IIf([MontantPrecedent]="",Null,([MontantVentes]-[MontantPrecedent])/[MontantPrecedent]) AS ProgressionVentes
FROM R_Ventes
ORDER BY AnneeVt, MoisVt;

Formule pour afficher les ventes du mois précédent :

MontantPrecedent: Format(RechDom("MontantVentes";"R_Ventes";VraiFaux([MoisVt]>1;"MoisVt=" & ([MoisVt]-1) & " and AnneeVt=" & [AnneeVt];"([MoisVt]=12) and AnneeVt=" & ([AnneeVt]-1)));"Monétaire")

Pour chaque ligne des ventes mensuelles, la fonction renvoie le montant des ventes du mois précédent. Cela permet de comparer le montant de chaque mois avec son précédent.

Résultats de la requête :

Affichage des données
Affichage des données

II-C-2. Sous-requête avec first

select first(expr) from domaine where Critères

Requête pour comparer les ventes :

Code SQL
Sélectionnez
SELECT MoisVentes, MontantVentes, Format((select First(MontantVentes) from R_Ventes As R1 where iif(R_Ventes.[MoisVt]>1,R1.[MoisVt]= (R_Ventes.[MoisVt]-1) and (R1.AnneeVt= R_Ventes.[AnneeVt]),(R1.[MoisVt]=12) and R1.AnneeVt= (R_Ventes.[AnneeVt]-1))),"Currency") AS MontantPrecedent, IIf([MontantPrecedent]="",Null,([MontantVentes]-[MontantPrecedent])/[MontantPrecedent]) AS ProgressionVentes
FROM R_Ventes
ORDER BY AnneeVt, MoisVt;

Des sous-requêtes complexes portant sur des tables contenant un nombre important d'enregistrements peuvent diminuer notablement les performances et même dans certains cas provoquer un plantage d'Access.

Formule pour afficher les ventes du mois précédent :

MontantPrecedent: Format((select First(MontantVentes) from R_Ventes As R1 where iif(R_Ventes.[MoisVt]>1,R1.[MoisVt]= (R_Ventes.[MoisVt]-1) and (R1.AnneeVt= R_Ventes.[AnneeVt]),(R1.[MoisVt]=12) and R1.AnneeVt= (R_Ventes.[AnneeVt]-1)));"Monétaire")

Pour chaque ligne de ventes mensuelles, la fonction renvoie le montant des ventes du mois précédent.

Résultats de la requête :

Affichage des données
Affichage des données

II-D. Requêtes mise à jour et fonctions de domaine

Comme ces fonctions permettent la modification des données, elles peuvent aussi être utilisées dans les requêtes de mise à jour contrairement aux sous-requêtes.

Reprenons les exemples de numérotation des enregistrements et de calcul des cumuls, et ajoutons deux champs supplémentaires à la table T_Paiement :

Champ

Type de données

Description

IdPaiement

Numéro-auto

Numéro d'enregistrement

NumPaiement

Entier long

Numéro du paiement de la facture

CumulPaiement

Monétaire

Cumul des paiements de la facture

On peut alors directement mettre à jour ces champs au moyen d'une requête utilisant les formules précédentes (1) et (2) :

Code SQL
Sélectionnez
UPDATE T_Paiement SET NumPaiement = DCount("*","T_Paiement","IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement]), CumulPaiement = DSum("MontantPaiement","T_Paiement","IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement]);

Le résultat obtenu sera le même que pour le calcul des montants cumulésmontants cumulés.

Si vous essayez de remplacer les fonctions CpteDom et SomDom par des sous-requêtes vous obtiendrez à l'exécution le message d'erreur « L'opération doit utiliser une requête qui peut être mise à jour ».

III. Création de sa propre fonction de domaine

Les fonctions de domaine écrites en VBA peuvent offrir les mêmes avantages que les fonctions vues précédemment, et même plus encore.

On peut par exemple, réaliser une fonction qui détermine le cumul des montants par facture, et indique l'avancement des paiements par rapport au total à payer.

Pour cela, il nous faut ajouter à notre base la table T_Facture pour enregistrer les montants des factures :

Champ

Type de données

Description

NumFacture

Numéro-auto

Numéro d'enregistrement

DateFacture

Date/Heure

Date de la facture

MontantFacture

Monétaire

Montant de la facture

Les tables T_Facture et T_Paiement sont reliées entre elles sur les champs NumFacture et IdFacture.

Code commenté de la fonction DProgress indiquant l'avancement des paiements au moyen d'une barre de progression :

fonction DProgress
Cacher/Afficher le codeSélectionnez

La fonction renvoie pg carrés noirs indiquant la progression des paiements par rapport au maximum et le reste des paiements est représenté par des carrés clairs avec un motif :

DProgress = String(pg, ChrW(&H2588)) & String(maximum - pg, ChrW(&H2591)) & " " & Format(pp, "0.00 %")

Puis, on construit une requête basée sur les deux tables reliées entre elles par une jointure :

Code SQL
Sélectionnez
SELECT T_Paiement.IdFacture, T_Paiement.IdPaiement, DCount("*","T_Paiement","IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement]) AS NumPaiement, T_Paiement.DatePaiement, T_Paiement.MontantPaiement, T_Paiement.ModePaiement, CCur(DSum("MontantPaiement","T_Paiement","IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement])) AS CumulPaiement, T_Facture.MontantFacture, DProgress("MontantPaiement","T_Paiement","IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement],[MontantFacture],20) AS AvancementPaiement
FROM T_Paiement INNER JOIN T_Facture ON T_Paiement.IdFacture = T_Facture.[NumFacture]
ORDER BY T_Paiement.IdFacture, T_Paiement.IdPaiement;

Formule pour afficher la progression des paiements :

AvancementPaiement: DProgress("MontantPaiement";"T_Paiement";"IdFacture=" & [IdFacture] & " and IdPaiement<=" & [IdPaiement];[MontantFacture];20)

Les trois premiers arguments sont les mêmes que pour la fonction de calcul des cumuls, les deux derniers arguments correspondent au montant total de la facture et à la valeur maximale de la barre de progression (ici 20).

Résultat de la requête :

Affichage des données
Affichage des données

IV. Conclusion

Même si les fonctions de domaine ont l'avantage de permettre la modification des données dans les formulaires, et seront donc préférées dans la plupart des cas, j'ai trouvé intéressant et instructif de présenter aussi des exemples de sous-requêtes pour montrer leur analogie avec les fonctions de domaine.

V. Base de données à télécharger

La base jointefonctions-domaine-vs-sous-requetes présente les différents exemples décrits dans le tutoriel, elle est au format accdb.

VI. Remerciements

Je tiens à remercier Arkham46, Pierre Fauconnier, argyronet, et zoom61 pour m'avoir conseillé pour la réalisation de cet article, ainsi que Claude Leloup pour sa relecture.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2019 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.