Un meilleur job mieux payé ?

Deviens chef de projet, développeur, ingénieur, informaticien

Mets à jour ton profil pro

ça m'intéresse

Gérer un troupeau de moutons avec Access

Image non disponible2e partie : l'exploitation des données

Vous pouvez lire cet article comme un exemple d'outil de gestion d'un troupeau de moutons.

Mais vous pouvez aussi l'aborder comme un exercice dirigé, vous y trouverez :

- quelques requêtes complexes, mais construites avec le QBE (l'interface proposée par Access) et dont la logique est expliquée en détail ;

- des formulaires sans source, avec des contrôles alimentés à l'aide de fonctions de domaine pour les données de base, et d'autres contrôles calculés à la « Excel » ;

- une astuce pour éviter les divisions par zéro dans les calculs de ratios.

Commentez Donner une note à l'article (5) 

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Prérequis

Ceci s'adresse au lecteur qui a pris connaissance de l'article précédent : Gérer un troupeau de moutons avec Access, nous y avons décrit les différents formulaires pour collecter les données.

II. Les données du modèle

Elles sont réparties dans différentes tables du modèle de données. Celles que nous utiliserons dans ce tutoriel sont encadrées :

Image non disponible

Les données de la base exemple ne sont sans doute pas représentatives d'une exploitation réelle. Elles sont là pour permettre de vérifier que les programmes calculent correctement.

III. Une fonction pour déterminer la catégorie d'un ovin à une date donnée

Pour l'analyse de l'évolution du troupeau, nous devons distinguer les ovins selon qu'ils sont mâles ou femelles, et adultes ou non.

III-A. Terminologie

Sexe Âge Catégorie
Femelle <1 an agnelle
Femelle ≥ 1 an brebis
Mâle <1 an agneau
Mâle ≥ 1 an bélier

III-B. Code de la fonction Categorie()

Fonction Categorie
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
Public Function Categorie(OvinPK As Long, DateDemande As Date) As String
  On Error GoTo GestionErreurs
  Dim iSexe As Integer
  Dim dNaissance As Date
  Dim d1erAnniv As Date
  
  'Son sexe
  iSexe = DLookup("tSexesFK", "tOvins", "tOvinsPK=" & OvinPK)
  
  'Sa date de naissance
  dNaissance = DLookup("DateNaiss", "tOvins", "tOvinsPK=" & OvinPK)
  If dNaissance > DateDemande Then Categorie = "Pas  !": Exit Function
  
  'Son 1er anniversaire
  d1erAnniv = DateSerial(Year(dNaissance) + 1, Month(dNaissance), Day(dNaissance))
  
  'Sa catégorie à la date demandée
  If d1erAnniv > DateDemande Then  ' Traitement si pas encore adulte
      If iSexe = 1 Then
          Categorie = "Agnelle": Exit Function
        Else
          Categorie = "Agneau": Exit Function
      End If
  
    Else                                             ' Traitement si adulte
      If iSexe = 1 Then
          Categorie = "Brebis": Exit Function
        Else
          Categorie = "Bélier"
      End If
  End If

GestionErreurs:
  Select Case Err.Number
    Case 0 'pas d'erreur
      Exit Function
    Case 94 'Pas trouvé dans tOvins
      MsgBox " La clé : " & OvinPK & " n'est pas trouvée dans tOvins", vbInformation
      Exit Function
    Case Else
      MsgBox "Erreur dans Categorie  " & Err.Number & " " & Err.Description
  End Select
End Function

Les commentaires inclus dans le code rendent celui-ci suffisamment compréhensible, même pour un non-initié.

Image non disponible
Pour un problème de compréhension du code dans un module, placez le curseur n'importe où dans l'instruction et pressez <F1>. L'aide Access s'ouvre alors à la bonne page.

On peut mettre plusieurs instructions sur une ligne à condition de les séparer par le caractère deux-points.

 
Sélectionnez
'comme ceci :
Categorie = "Agnelle": Exit Function

'au lieu de ceci :
Categorie = "Agnelle" 
Exit Function

IV. Le formulaire fInventaire

L'idée est de justifier (au sens comptable du terme) la composition du troupeau entre deux dates (par défaut la dernière saison).

IV-A. Présentation

Image non disponible

Il s'agit d'un formulaire indépendant : il n'a pas de source.

Les cellules en vert sont alimentées soit :

- à l'aide d'une fonction de domaine :

Image non disponible

- par combinaison d'autres contrôles :

Image non disponible

fInventaire contient deux sous-formulaires (sfInvenSorties et sfInvenEntrees) qui donnent le détail des sorties et des entrées.

IV-B. Les données pour alimenter fInventaire

IV-B-1. tInvenEntrees et tInvenEntrees

L'ouverture du formulaire provoque la création de deux tables tInvenEntrees et tInvenEntrees.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
  Call Rafraichir
End Sub

Public Sub Rafraichir()
  Dim ctl As Control
  Dim sSql As String
  
  'Recréer la table tInvenEntrees
  sSql = "SELECT categorie([tOvinsPK],[DateEntree]) AS Categorie, tOvins.* INTO tInvenEntrees " _
          & "FROM tOvins " _
          & " WHERE tOvins.DateEntree>=#" & Format([Forms]![fInventaire]![txtDebut], "mm/dd/yy") & "# " _
                & "And tOvins.DateEntree<=#" & Format([Forms]![fInventaire]![txtFin], "mm/dd/yy") & "#" _
                & "And tOvins.Fictif=False;"
  DoCmd.SetWarnings False
  DoCmd.RunSQL sSql
  DoCmd.SetWarnings True
  For Each ctl In Me.Controls
    If ctl.Name Like "txt*" Or ctl.Name Like "CTNRsf*" Then ctl.Requery
  Next ctl
  
  'Recréer la table tInvenSorties
  sSql = "SELECT categorie([tOvinsPK],[DateSortie]) AS Categorie, tOvins.* INTO tInvenSorties " _
           & "FROM tOvins " _
           & "WHERE tOvins.DateSortie >=#" & Format([Forms]![fInventaire]![txtDebut], "mm/dd/yy") & "# " _
                      & "AND tOvins.DateSortie <=#" & Format([Forms]![fInventaire]![txtFin], "mm/dd/yy") & "# " _
                      & "AND tOvins.tCausesSortieFK<>6 " _
                      & "And tOvins.Fictif=False;"
  DoCmd.SetWarnings False
  DoCmd.RunSQL sSql
  DoCmd.SetWarnings True
End Sub

Commentaires du code

13-17 : par exemple si la période est exprimée comme ceci :

Image non disponible

la requête construite à la volée correspond à ceci :

Image non disponible

26-31 : la requête construite à la volée correspond à ceci :

Image non disponible

Ces deux tables sont supprimées lors de la fermeture du formulaire.

 
Sélectionnez
Private Sub Form_Close()
  'Supression des tables tInvenEntrees et tInvenSorties
  DoCmd.DeleteObject acTable, "tInvenEntrees"
  DoCmd.DeleteObject acTable, "tInvenSorties"
End Sub

Nous utiliserons aussi quatre requêtes enregistrées :

Image non disponible

IV-B-2. rInvenDebut

Image non disponible

IV-B-3. rInvenFin

Image non disponible

IV-B-4. rInvenAgneauxVersBeliers

Il s'agit de détecter les mâles qui durant la période sont passés à l'âge adulte.

C'est l'union de deux requêtes :

- la première ramène les mâles présents pendant toute la période qui ont changé de catégorie :

Image non disponible

- la seconde ramène les agneaux entrés pendant la période devenus adultes à la fin de celle-ci :

Image non disponible

L'union des deux requêtes s'écrit comme ceci

rInvenAgneauxVersBeliers
Sélectionnez
SELECT rInvenDebut.tOvinsPK
FROM rInvenDebut INNER JOIN rInvenFin ON rInvenDebut.tOvinsPK = rInvenFin.tOvinsPK
WHERE (((rInvenDebut.Categorie)="agneau") AND ((rInvenFin.Categorie)="bélier")) UNION SELECT tOvins.tOvinsPK
FROM tOvins INNER JOIN rInvenFin ON tOvins.tOvinsPK = rInvenFin.tOvinsPK
WHERE (((tOvins.DateEntree)>[Formulaires]![fInventaire]![txtDebut]) AND ((tOvins.DateSortie)>[Formulaires]![fInventaire]![txtFin] Or (tOvins.DateSortie) Is Null) AND ((Categorie([tOvins].[tOvinsPK],[tOvins].[DateEntree]))="Agneau") AND ((rInvenFin.Categorie)="Bélier"));

IV-B-5. rInvenAgnellesVersBrebis

Il s'agit de détecter les femelles qui durant la période sont passées à l'âge adulte.

Même principe que pour les mâles.

L'union des deux requêtes s'écrit comme ceci :

rInvenAgnellesVersBrebis
Sélectionnez
SELECT rInvenDebut.tOvinsPK
FROM rInvenDebut INNER JOIN rInvenFin ON rInvenDebut.tOvinsPK = rInvenFin.tOvinsPK
WHERE (((rInvenDebut.Categorie)="agnelle") AND ((rInvenFin.Categorie)="brebis")) UNION SELECT tOvins.tOvinsPK
FROM tOvins INNER JOIN rInvenFin ON tOvins.tOvinsPK = rInvenFin.tOvinsPK
WHERE (((tOvins.DateEntree)>[Formulaires]![fInventaire]![txtDebut]) AND ((tOvins.DateSortie)>[Formulaires]![fInventaire]![txtFin] Or (tOvins.DateSortie) Is Null) AND ((Categorie([tOvins].[tOvinsPK],[tOvins].[DateEntree]))="Agnelle") AND ((rInvenFin.Categorie)="Brebis"));

IV-C. Le sous-formulaire sfInvenSorties

Image non disponible

IV-C-1. Source

Source de sfInvenSorties
Sélectionnez
SELECT tCausesSortie.CauseSortie, DCount("*","tInvenSorties","Categorie='Agnelle' AND tCausesSortieFK=" & [tCausesSortiePK]) AS Agnelles, DCount("*","tInvenSorties","Categorie='Brebis' AND tCausesSortieFK=" & [tCausesSortiePK]) AS Brebis, DCount("*","tInvenSorties","Categorie='agneau' AND tCausesSortieFK=" & [tCausesSortiePK]) AS Agneaux, DCount("*","tInvenSorties","Categorie='Bélier' AND Fictif = false AND tCausesSortieFK=" & [tCausesSortiePK]) AS Beliers FROM tCausesSortie WHERE (((tCausesSortie.tCausesSortiePK)<>6)) ORDER BY tCausesSortie.OrdreInven;
Image non disponible

IV-D. Le sous-formulaire sfInvenEntrees

Image non disponible

Sa construction est calquée sur celle de sfInvenSorties.

V. Le fomulaire fRepro

On affiche dans ce formulaire une série de nombres et ratios qui caractérisent les femelles du troupeau pendant une saison (par défaut, la dernière écoulée).

Image non disponibleDonne la proportion de femelles qui ont été mises en lutte durant la saison.

Image non disponibleRenseigne les agnelages intervenus. Il se peut qu'ils soient supérieurs au nombre de mises en lutte si des femelles ont été inséminées artificiellement ou achetées déjà en cours de gestation.

Image non disponibleRenseigne le nombre moyen de nouveau-nés par mise bas.

Image non disponibleNombre de nouveau-nés qui sont morts avant la fin de la saison.

Image non disponibleNombre de nouveau-nés qui ont survécu.

V-A. Présentation

Image non disponible

Tout comme le formulaire précédent, fRepro n'a pas de source.

Les contrôles du formulaire sont alimentés soit :
- par des fonctions de domaine (sur des requêtes décrites plus bas) ;
- soit par combinaison d'autres contrôles :

Image non disponible

Pour éviter les cas où on serait amené à diviser par zéro :

ZeroToUn()
Sélectionnez
Public Function ZeroToUn(Diviseur As Double) As Double
  'Pour éviter de diviser par zéro
  If Diviseur = 0 Then
      ZeroToUn = 1
    Else
      ZeroToUn = Diviseur
  End If
End Function

Diviser un nombre par un, ça ne mange pas de pain !

V-B. Les données pour alimenter fRepro

Nous utilisons sept requêtes enregistrées pour préparer les données que nous importerons ensuite dans le formulaire à l'aide de fonctions de domaine :

Image non disponible

V-B-1. rRepro01FemellesPresentes

Image non disponible
Image non disponible

Remarquez les deux lignes de critères :

Image non disponible

Cette requête va ramener deux sortes d'enregistrements :

- ceux qui vérifient les conditions de la 1re ligne ;

ET

- ceux qui vérifient les conditions de la seconde.

Commentaires des colonnes

Image non disponible On ramène uniquement les femelles.

Image non disponible Il s'agit de la catégorie (agnelle ou brebis) au début de la saison.

Image non disponible et Image non disponible expriment que cette femelle était entrée avant le début et que, si elle est sortie, c'est après le début.

Image non disponible

Avec Nz() on substitue à la valeur Null éventuelle de DateSortie une date loin dans le futur (en l'occurrence 1/1/2100). Cela nous épargne de devoir traiter un VraiFaux() supplémentaire.

Image non disponible On exclut du bilan de reproduction les femelles qui ont été destinées à la boucherie.

Image non disponible Il s'agit des femelles entrées dans le troupeau en cours de saison, MAIS évidemment pas les agnelles nouveau-nées de cette saison !

V-B-2. rRepro02MisesEnLutte

Image non disponible

Parmi les femelles ramenées par la requête rRepro01FemellesPresentes, on retient celles qui ont été mises en lutte durant la période.

V-B-3. rRepro03Agnelages

Image non disponible

Pour chaque femelle mise en lutte durant la période, on ramène une ligne par femelle ayant mis bas.

V-B-4. rRepro04Agnele2X

Image non disponible

Parmi les femelles de la requête rRepro03Agnelages, on ramène celles qui y figurent plus d'une fois.

(Sachant qu'une gestation dure environ 150 jours et qu'un délai de 75 jours sépare une mise bas et la mise en lutte suivante, en pratique deux mises bas par saison est un maximum.)

V-B-5. rRepro05Improductives

Image non disponible

Cette requête va donc ramener les femelles présentes qui ne se retrouvent pas parmi celles ayant mis bas.

V-B-6. rRepro06AgneauxNes

Image non disponible

Cette requête ramène donc les nouveau-nés pour chaque agnelage intervenu durant la saison.

V-B-7. rRepro07AgneauxMorts

Image non disponible

Désolé de terminer cette liste sur une note triste…

V-C. Code associé à fRepro

Module de fRepro
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Option Compare Database
Option Explicit

Private Sub txtDebut_AfterUpdate()
  Dim ctl As Control
  Me.txtFin = DateSerial(Year(Me.txtDebut) + 1, Month(Me.txtDebut), Day(Me.txtDebut) - 1)
  For Each ctl In Me.Controls
    If ctl.Name Like "txt####" Then ctl.Requery
  Next ctl
End Sub

Private Sub txtFin_AfterUpdate()
  Dim ctl As Control
  Me.txtDebut = DateSerial(Year(Me.txtFin) - 1, Month(Me.txtFin), Day(Me.txtFin) + 1)
  For Each ctl In Me.Controls
    If ctl.Name Like "txt####" Then ctl.Requery
  Next ctl
End Sub

Commentaires du code

À l'ouverture du formulaire, la période indiquée correspond à la dernière saison écoulée. 

Image non disponible

L'utilisateur peut changer cette période à la condition que ce qu'il propose corresponde à une durée de 1 an.

6 et 14 : après mise à jour d'une des deux dates, l'autre est automatiquement adaptée pour que l'écart entre début et fin soit d'une année.

7-9 et 15-17 : on réactualise la valeur des contrôles de données (que nous avons pris la précaution de nommer « txt » suivi de quatre chiffres).

VI. Le formulaire fPresentsADate

C'est un outil qui consiste à afficher la composition du troupeau à une date choisie par l'utilisateur.

VI-A. Présentation

Image non disponible

VI-B. La source de fPresentsADate

Image non disponible

Image non disponible Cette requête ramène donc toutes les colonnes de la table tOvins, la catégorie de l'animal à la date indiquée dans le formulaire, la race en clair et la date de sortie (éventuelle).

Image non disponible Pour les ovins (non fictifs) qui étaient présents à cette date (déjà entrés et pas encore sortis).

Image non disponible Les lignes sont triées de manière à présenter dans l'ordre : les brebis, les agnelles, les béliers et les agneaux et, à l'intérieur des catégories, par numéro de travail.

VI-C. La mise en forme conditionnelle

Chaque catégorie s'affiche dans une couleur qui lui est propre

Image non disponible

En arrière-plan nous avons une zone de texte indépendante txtCategorie dont la couleur de fond originale correspond à celle choisie pour les agneaux et que nous modifions grâce à la mise en forme conditionnelle en fonction de la valeur de la colonne [Categorie] de chaque enregistrement.

Image non disponible

Les autres contrôles sont superposés à cette zone de texte et leur propriété Style fond a la valeur « Transparent » :

Image non disponible

Cette astuce donne l'illusion que les lignes du formulaire sont colorées selon la catégorie.

Remarquez que le texte sur les lignes des béliers s'affiche en blanc :

Image non disponible

Pour afficher le nombre dans chaque catégorie

Image non disponible

VI-D. Code associé à fPresentsADate

 
Sélectionnez
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
  Me.Requery
End Sub

Private Sub txtDate_AfterUpdate()
  Me.Requery
End Sub

Après chaque modification de la date, on provoque la réactualisation du formulaire.

N.B. À l'ouverture, il faut aussi actualiser, sinon on obtiendrait ceci :

Image non disponible

Cela tient au fait qu'Access attribue la source à un instant où il n'a pas encore aménagé la valeur par défaut de txtDate. La requête rfPresentsADate ne ramène donc aucun enregistrement (txtDate étant Null).

VII. Téléchargement

La base de données au format Access 2000 se trouve ici.

VIII. Remerciements

Merci à foster53 qui m'a expliqué l'aspect métier.

Merci à Malick Seck (milkoseck) pour la correction orthographique.

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

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2015 Claude Leloup. 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.