Fonctions de domaine vs sous-requêtes

Denis Hulo

Objectif : apprendre à mettre en œuvre les fonctions de domaine et les sous-requêtes à l'aide d'exemples simples.

Niveau requis : confirmé.

Commentez cet article : Commentez

- Titre : Fonctions de domaine vs sous-requêtes

- Auteur : Denis Hulo

- Parution : 1 août 2019

- Public visé :

- Licence : Copyright ® 2019 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site et 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.

Sommaire

- I. Introduction

- II. Exemples d'utilisation

- II-A. Numérotation des enregistrements

- II-A-1. Fonction de domaine CpteDom

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

- II-B. Cumul des montants

- II-B-1. Fonction de domaine SomDom

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

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

- II-C-1. Fonction de domaine RechDom

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

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

- III. Création de sa propre fonction de domaine

- IV. Conclusion

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

- VI. Remerciements

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

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

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

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

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

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

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

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

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 :

(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

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

Ajoutons à 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.

On crée ensuite une requête R_Ventes réalisant la somme des ventes par mois :

Code SQL

SELECT Format([DateFacture],"mmmm yyyy") AS MoisVentes, Sum(CCur([MontantFacture])) AS MontantVentes, Year([DateFacture]) AS AnneeVt, Month([DateFacture]) AS MoisVt
FROM T_Facture
GROUP BY Format([DateFacture],"mmmm yyyy"), Year([DateFacture]), Month([DateFacture])
ORDER BY Year([DateFacture]), Month([DateFacture]);

Enfin, on construit la requête permettant de comparer les ventes mensuelles :

Code SQL

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

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

select first(expr) from domaine where Critères

Requête pour comparer les ventes :

Code SQL

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

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

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

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

fonction DProgress

Public Function DProgress(expr As String, domaine As String, critere As String, total As Double, maximum As Long) As String
'la fonction renvoie dans une chaîne de caractères la progression des paiements par rapport au total de la facture
'expr: argument identifiant le champ à utiliser pour faire la somme
'domaine: argument identifiant le jeu d'enregistrements, à savoir un nom de table ou de requête
'critere: expression chaîne pour limiter la plage de données
'total: montant total de la facture
'maximum: valeur maximale de la barre de progression
Dim db As DAO.Database ' variable pour faire référence à la base de données
Dim rs As DAO.Recordset ' variable identifiant le recordset
Dim pp As Double ' variable indiquant la proportion payée par rapport au total
Dim pg As Long ' variable indiquant la progression par rapport à la taille de la barre
Dim pr As Long ' variable indiquant la progression restante par rapport à la taille de la barre

Set db = CurrentDb ' référence à la base courante
' ouverture du recordset basé sur le code SQL effectuant la somme des paiements
Set rs = db.OpenRecordset("select sum(" & expr & ") from " & domaine & " where " & critere, dbOpenSnapshot)

pp = rs.Fields(0) / total ' proportion par rapport au montant total
pg = CInt(pp * maximum) ' progression par rapport à la valeur maxi de la barre
pr = maximum - pg ' progression restante par rapport à la valeur maxi de la barre

If pr < 0 Then ' si on dépasse 100% de progression
pr = 0 ' on met le reste de la progression à 0
End If

' On renvoie le résultat de l'avancement des paiements dans une chaîne de caractères
DProgress = String(pg, ChrW(&H2588)) & String(pr, ChrW(&H2591)) & " " & Format(pp, "0.00 %")

' On ferme et libère les variables
rs.Close
Set rs = Nothing

Set db = Nothing
End Function

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(pr, 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

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

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