Initiation aux bases de donnes et la programmation vnementielle

1
Initiation aux bases de donnéeset à la
programmation événementielle

• VBA sous ACCESS
• Cours N 8

Support de cours rédigé par Bernard
COFFIN Université Paris 10 Nanterre
2007/2008
2
Des calculs sur les informations de la base de
données
Les requêtes sélection permettent dobtenir des
champs dont la valeur est calculée à partir de
plusieurs données La requête commence par
construire la relation R0 par produit cartésien,
projection, restriction

• Le calcul se fait sur les données dun seul
  enregistrement de la relation R0
• Le résultat est un champ (calculé) ajouté à ce
  même enregistrement
• Voir le cours N 4

3
Calculs  statistiques 

• Le calcul se fait sur les données dun groupe de
  plusieurs enregistrements de la relation R0
• Ces enregistrements doivent avoir un point
  commun des champs qui ont la même valeur
• Ils sont agrégés pour nen faire plus quun qui
  comprend
• Une partie des champs communs (éventuellement
  tous)
• Les résultats des calculs

Les enregistrements ainsi décrits forment une
nouvelle relation R1 cest elle qui est le
résultat de la requête
4
Syntaxe
Select liste des champs de la projection et des
fonctions dagrégat From liste des relations dont
on fait le produit cartésien Where clause de
restriction éventuelle (ne peut pas porter sur
les fonctions dagrégat) Group By liste des
champs servant à lagrégation de plusieurs
enregistrements Having autre clause de
restriction (celle-ci peut porter sur les
fonctions et champs dagrégat) Lordre des
clauses doit être respecté On peut ajouter à la
fin une clause de tri (Order By)
5
La fonction dagrégat
Calcule une valeur à partir des valeurs de tous
les enregistrements agrégés Syntaxe nom de la
fonction (expression)
Les fonctions disponibles sont Sum somme Avg
moyenne Min le plus petit Max le plus
grand Count compte StDev écart type Var
variance First premier Last dernier
6
Les nouvelles clauses
Group By liste des champs servant à lagrégation
de plusieurs enregistrements Tous les
enregistrements de la relation de base R0 dont
tous les champs qui suivent  Group By  ont la
même valeur (dun enregistrement à lautre) sont
agrégés pour ne former quun seul enregistrement
dans la relation résultat de la requête R1 Having
autre clause de restriction portant sur les
fonctions dagrégat et les champs servant à
lagrégation La syntaxe est la même que pour
une clause Where, mais elle porte sur les
fonctions et champs dagrégat de la
projection Elle ne peut pas porter sur des champs
qui ne servent pas à lagrégation (utiliser Where
pour ces restrictions-là)
7
Important !
Tous les champs sur lesquels on fait la
projection (clause Select) doivent servir dans la
clause dagrégat (Group By) ou être le résultat
dune fonction dagrégat Mais un champ peut
figurer dans la clause dagrégat sans servir à la
projection De même, les champs utilisés dans la
restriction Where peuvent ne pas servir à la
projection (comme dhabitude)
8
Exemple 1
Les noms symboliques Identité et Nombre_oeuvres
ne sont pas utilisables dans les autres clauses
Nombre duvres par auteur ? Select N auteur,
Nom auteur " "  Prénom auteur As Identité,
count(N uvre) As Nombre_uvres From
tabAuteur, tabuvre Where Référence auteur
N auteur Group By N auteur, Nom auteur
" "  Prénom auteur
9
Supprimer le champ N auteur ? Select N
auteur, Nom auteur " "  Prénom auteur As
Identité, count(N uvre) As Nombre_uvres
From tabAuteur, tabuvre Where Référence
auteur N auteur Group By N auteur, Nom
auteur " "  Prénom auteur Sil ny a pas
dhomonymes, même nom et même prénom, on aura
autant denregistrements dans la relation
résultant de la requête, mais on ne pourra pas
utiliser le N auteur dans un objet dont la
requête serait la source ou le contenu
10
Se contenter du Nom auteur au lieu du champ
calculé ? Select N auteur, Nom auteur,
count(N uvre) As Nombre_ uvres From
tabAuteur, tabuvre Where Référence auteur
N auteur Group By N auteur, Nom
auteur En cas dhomonymes, même nom (par
exemple Charlotte et Emily BRONTE), les
enregistrements de tous les homonymes seront
agrégés dans la relation résultant de la
requête On aura donc un seul enregistrement pour
les deux surs Bronte Mais si on ajoute N
auteur dans la clause dagrégat on aura un
enregistrement pour chacune delle avec le même
nom, et des valeurs de Nombre_uvres différentes
(potentiellement) Si on ajoute N auteur dans
la projection MAIS PAS dans la clause
dagrégat la requête est fausse (erreur de
syntaxe !)
11
Exemple 2
calculer le montant total dune commande
12
Pour chaque ligne de la commande (un produit
commandé), il faut faire le produit de la
quantité commandée par le prix unitaire Puis il
faut faire la somme de ces valeurs pour
lensemble de la commande Select Réf commande,
sum(Quantité commandéePrix unitaire) as
Prix_total From tabLien_Cde_Pdt, tabProduit Where
Réf produit Code produit Group By Réf
commande
13
Chiffre daffaire du client ? Cest la somme des
montants de toutes les commandes quil a
passées Select Réf client, sum(Quantité
commandéePrix unitaire) as Chiffre_affaire Fro
m tabCommande, tabLien_Cde_Pdt, tabProduit Where
N commande Réf commande and Réf produit
Code produit Group By Réf client
14
Utilisation dans un formulaire Lobligation
dutiliser les champs de la projection dans
lagrégation empêche souvent de faire de la
requête avec calcul statistique la source du
formulaire principal On utilise alors un
contrôle dont la requête puisse être la source
(sous-formulaire) ou le contenu (zone de
liste) Il faut veiller à ce que les résultats
affichés par ce moyen soient mis à jour
convenablement ! Exemple Saisie dune commande
client. On a ajouté un champ taux de remise
exceptionnelle à la table commande (ce taux de
remise est valable pour toute la commande et
uniquement pour elle) Ce taux est décidé par le
commercial qui utilise lapplication pour cela
il doit visualiser à tout moment le montant de la
commande et le chiffre daffaire du client
(incluant la commande en cours de saisie)
15
Conception du formulaire La source du
formulaire est une requête fondée sur la table de
description des commandes (tabCommande) Des
zones de texte permettent de saisir la date de la
commande et le taux de remise accordé (valeur par
défaut zéro) elles sont liées aux champs
correspondants Une zone de liste déroulante
permet de sélectionner le client (contenu classé
par nom et adresse) Un sous-formulaire lié au
formulaire principal (lien père / fils) permet la
saisie des lignes de la commande et laffichage
des prix unitaires Une zone de liste déroulante
permet de sélectionner le produit (contenu classé
par nom) Le prix unitaire est affiché au moyen
dune zone de texte liée son format est celui
dune étiquette (il nest pas modifiable par ce
traitement) La quantité commandée est saisie
grâce à une zone de texte liée au champ
correspondant Deux sous-formulaires permettent
dafficher respectivement le chiffre daffaire du
client (commande en cours non comprise) et les
montants totaux de la commande (avant et après
réduction)
16
(No Transcript)
17
La source du formulaire formSaisie_commande est
une requête fondée sur la table de description
des commandes (tabCommande) SELECT FROM
tabCommande Des zones de texte permettent de
saisir la date de la commande et le taux de
remise accordé (valeur par défaut zéro) elles
sont liées aux champs correspondants

• ztDate Source contrôle Date commande
• ztRemise Source contrôle Taux_remise_exceptio
  nnelle

Une zone de texte permet dafficher le numéro de
la facture ztNuméro Source contrôle N
commande Son aspect est celui dune étiquette
(pas de saisie possible) ce contrôle ne peut
pas devenir actif Private Sub
ztNuméro_GotFocus() Choix_client.SetFocus End Sub
18
Une zone de liste déroulante permet de
sélectionner le client (contenu classé par nom et
adresse) Choix_client
Source contrôle Réf client Contenu SELECT
N client, Nom client " " Adresse
FROM tabClient ORDER BY Nom client, Adresse
Colonne liée 1 Nbre colonnes 2 Largeur
colonnes 0cm 7cm
19
Un sous-formulaire lié au formulaire principal
(lien père / fils) permet la saisie des lignes de
la commande et laffichage des prix
unitaires sfLigne_commande
Objet lié formSaisie_ligne Source du formulaire
(formSaisie_ligne) SELECT tabLien_Cde_Pdt.,
Prix unitaire FROM tabLien_Cde_Pdt INNER JOIN
tabProduit ON tabProduit.Code
produittabLien_Cde_Pdt.Réf_produit ORDER BY
Réf_produit Champ père N commande Champ
fils Réf_commande
20
Une zone de liste déroulante permet de
sélectionner le produit (contenu classé par
nom) Choix_produit
Source contrôle Réf_produit Contenu SELECT
Code produit, Nom produit FROM tabProduit
ORDER BY Nom produit Colonne liée 1 Nbre
colonnes 2 Largeur colonnes 0cm 7cm
21
Le prix unitaire est affiché au moyen dune zone
de texte liée son format est celui dune
étiquette (il nest pas modifiable par ce
traitement) ztPrix Source contrôle Prix
unitaire Format monétaire Ce contrôle ne peut
pas devenir actif Private Sub ztPrix_Enter()
Choix_produit.SetFocus End Sub La quantité
commandée est saisie grâce à une zone de texte
liée au champ correspondant ztQuantité Source
contrôle Quantité commandée
22
Deux sous-formulaires permettent dafficher
respectivement le chiffre daffaire du client
(commande en cours non comprise) et les montants
totaux de la commande (avant et après réduction)

• Chiffre daffaire sfChiffre_daffaires

Objet lié formChiffre_daffaires Source du
formulaire (formChiffre_daffaires) SELECT
Réf_client, Sum(Quantité commandéePrix
unitaire(1-Taux_remise_Exceptionnelle/100)) AS
Total_client FROM tabCommande, tabLien_Cde_Pdt,
tabProduit WHERE N commandeltgtforms!formsaisie_
commande!ztNuméro And N commandeRéf_command
e And Réf_produitCode produit GROUP BY
Réf_client Champ père formSaisie_commande.R
éf_client Champ fils formChiffre_daffaires.
Réf_client Une zone de texte liée
ztChiffre Source contrôle Total_client
23

• Chiffre daffaire sfTotal

Objet lié formTotal_commande Source du
formulaire (formTotal_commande) SELECT
Réf_commande, Sum(Quantité commandéePrix
unitaire) AS Prix_total FROM tabLien_Cde_Pdt,
tabProduit WHERE Réf_produitCode produit
GROUP BY Réf_commande Champ père
Réf_commande Champ fils N commande Une
zone de texte liée ztTotal_brut Source
contrôle Prix_total Une zone de texte liée
ztChiffre Source contrôle Prix_total(1-For
mulaires!formSaisie_commande!ztRemise.Value/100)
24
Il faut veiller à ce que les résultats affichés
par ce moyen soient mis à jour convenablement
! Si on modifie un enregistrement de la table
tabLien_Cde_Pdt, a fortiori si on en crée un
nouveau, il faut recalculer le montant total de
la commande Private Sub Form_AfterUpdate()
Forms!formSaisie_commande!sfTotal.Requery End
Sub On ne pourrait pas modifier le chiffre
daffaires parce que lenregistrement du
formulaire principal pourrait ne pas avoir été
mis à jour (taux de remise) et on obtiendrait des
données affichées incohérentes. Cest pour cela
quon naffiche que le montant du chiffre
daffaires ne comprenant pas la commande en
cours.
25
Il faut veiller à ce que les résultats affichés
par ce moyen soient mis à jour convenablement
! Si on modifie la valeur du taux de remise
exceptionnelle ztRemise, il faut recalculer le
montant total de la commande Private Sub
ztRemise_BeforeUpdate(Cancel As Integer)
sfTotal.Requery End Sub Pour empêcher
lutilisateur dessayer datteindre le chiffre
daffaires Private Sub sfChiffre_d_affaires_Ent
er() Screen.PreviousControl.SetFocus End
Sub Cette procédure rend actif le dernier
contrôle qui létait sur lécran (Screen)
26
(No Transcript)