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 :
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 :
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 :
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 :
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 :
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 :
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 :
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 :
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 :
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 :
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) :
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.
Code commenté de la fonction DProgress indiquant l'avancement des paiements au moyen d'une barre de progression :
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 :
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 :
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.