Fonctions de domaine vs sous-requêtes avec Access

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-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 ⁽¹⁾ :

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 :

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 :

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 :

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 ⁽²⁾ :

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 :

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 :

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

Sélectionnez

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

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 :

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 :

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

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

Cacher/Afficher le codeSélectionnez

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

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 :

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.

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

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.

Fonctions de domaine vs sous-requêtes

L'auteur

L'article

Liens sociaux

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▲