Gérer un troupeau de moutons avec Access

2e partie : l'exploitation des données

Claude Leloup

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

- Titre : Gérer un troupeau de moutons avec Access

- Auteur : Claude Leloup

- Parution : 8 octobre 2015

- Licence : 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.

Sommaire

- I. Prérequis

- II. Les données du modèle

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

- III-A. Terminologie

- III-B. Code de la fonction Categorie()

- IV. Le formulaire fInventaire

- IV-A. Présentation

- IV-B. Les données pour alimenter fInventaire

- IV-B-1. tInvenEntrees et tInvenEntrees

- IV-B-2. rInvenDebut

- IV-B-3. rInvenFin

- IV-B-4. rInvenAgneauxVersBeliers

- IV-B-5. rInvenAgnellesVersBrebis

- IV-C. Le sous-formulaire sfInvenSorties

- IV-C-1. Source

- IV-D. Le sous-formulaire sfInvenEntrees

- V. Le fomulaire fRepro

- V-A. Présentation

- V-B. Les données pour alimenter fRepro

- V-B-1. rRepro01FemellesPresentes

- V-B-2. rRepro02MisesEnLutte

- V-B-3. rRepro03Agnelages

- V-B-4. rRepro04Agnele2X

- V-B-5. rRepro05Improductives

- V-B-6. rRepro06AgneauxNes

- V-B-7. rRepro07AgneauxMorts

- V-C. Code associé à fRepro

- VI. Le formulaire fPresentsADate

- VI-A. Présentation

- VI-B. La source de fPresentsADate

- VI-C. La mise en forme conditionnelle

- VI-D. Code associé à fPresentsADate

- VII. Téléchargement

- VIII. Remerciements

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 :

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

1. Public Function Categorie(OvinPK As Long, DateDemande As Date) As String

2. On Error GoTo GestionErreurs

3. Dim iSexe As Integer

4. Dim dNaissance As Date

5. Dim d1erAnniv As Date

7. 'Son sexe

8. iSexe = DLookup("tSexesFK", "tOvins", "tOvinsPK=" & OvinPK)

10. 'Sa date de naissance

11. dNaissance = DLookup("DateNaiss", "tOvins", "tOvinsPK=" & OvinPK)

12. If dNaissance > DateDemande Then Categorie = "Pas né !": Exit Function

14. 'Son 1er anniversaire

15. d1erAnniv = DateSerial(Year(dNaissance) + 1, Month(dNaissance), Day(dNaissance))

17. 'Sa catégorie à la date demandée

18. If d1erAnniv > DateDemande Then  '1° Traitement si pas encore adulte

19. If iSexe = 1 Then

20. Categorie = "Agnelle": Exit Function

21. Else

22. Categorie = "Agneau": Exit Function

23. End If

25. Else                                             '2° Traitement si adulte

26. If iSexe = 1 Then

27. Categorie = "Brebis": Exit Function

28. Else

29. Categorie = "Bélier"

30. End If

31. End If

33. GestionErreurs:

34. Select Case Err.Number

35. Case 0 'pas d'erreur

36. Exit Function

37. Case 94 'Pas trouvé dans tOvins

38. MsgBox " La clé : " & OvinPK & " n'est pas trouvée dans tOvins", vbInformation

39. Exit Function

40. Case Else

41. MsgBox "Erreur dans Categorie N° " & Err.Number & " " & Err.Description

42. End Select

43. End Function

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

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.

'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

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 :

- par combinaison d'autres contrôles :

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.

1. Option Compare Database

2. Option Explicit

4. Private Sub Form_Open(Cancel As Integer)

5. Call Rafraichir

6. End Sub

8. Public Sub Rafraichir()

9. Dim ctl As Control

10. Dim sSql As String

12. 'Recréer la table tInvenEntrees

13. sSql = "SELECT categorie([tOvinsPK],[DateEntree]) AS Categorie, tOvins.* INTO tInvenEntrees " _

14. & "FROM tOvins " _

15. & " WHERE tOvins.DateEntree>=#" & Format([Forms]![fInventaire]![txtDebut], "mm/dd/yy") & "# " _

16. & "And tOvins.DateEntree<=#" & Format([Forms]![fInventaire]![txtFin], "mm/dd/yy") & "#" _

17. & "And tOvins.Fictif=False;"

18. DoCmd.SetWarnings False

19. DoCmd.RunSQL sSql

20. DoCmd.SetWarnings True

21. For Each ctl In Me.Controls

22. If ctl.Name Like "txt*" Or ctl.Name Like "CTNRsf*" Then ctl.Requery

23. Next ctl

25. 'Recréer la table tInvenSorties

26. sSql = "SELECT categorie([tOvinsPK],[DateSortie]) AS Categorie, tOvins.* INTO tInvenSorties " _

27. & "FROM tOvins " _

28. & "WHERE tOvins.DateSortie >=#" & Format([Forms]![fInventaire]![txtDebut], "mm/dd/yy") & "# " _

29. & "AND tOvins.DateSortie <=#" & Format([Forms]![fInventaire]![txtFin], "mm/dd/yy") & "# " _

30. & "AND tOvins.tCausesSortieFK<>6 " _

31. & "And tOvins.Fictif=False;"

32. DoCmd.SetWarnings False

33. DoCmd.RunSQL sSql

34. DoCmd.SetWarnings True

35. End Sub

Commentaires du code

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

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

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

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

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 :

IV-B-2 - rInvenDebut

IV-B-3 - rInvenFin

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 :

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

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

rInvenAgneauxVersBeliers

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

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

IV-C-1 - Source

Source de sfInvenSorties

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;

IV-D - Le sous-formulaire sfInvenEntrees

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

Donne la proportion de femelles qui ont été mises en lutte durant la saison.

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

Renseigne le nombre moyen de nouveau-nés par mise bas.

Nombre de nouveau-nés qui sont morts avant la fin de la saison.

Nombre de nouveau-nés qui ont survécu.

V-A - Présentation

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 :

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

ZeroToUn()

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 :

V-B-1 - rRepro01FemellesPresentes

Remarquez les deux lignes de critères :

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

On ramène uniquement les femelles.

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

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

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.

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

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

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

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

V-B-4 - rRepro04Agnele2X

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

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

V-B-6 - rRepro06AgneauxNes

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

V-B-7 - rRepro07AgneauxMorts

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

V-C - Code associé à fRepro

Module de fRepro

1. Option Compare Database

2. Option Explicit

4. Private Sub txtDebut_AfterUpdate()

5. Dim ctl As Control

6. Me.txtFin = DateSerial(Year(Me.txtDebut) + 1, Month(Me.txtDebut), Day(Me.txtDebut) - 1)

7. For Each ctl In Me.Controls

8. If ctl.Name Like "txt####" Then ctl.Requery

9. Next ctl

10. End Sub

12. Private Sub txtFin_AfterUpdate()

13. Dim ctl As Control

14. Me.txtDebut = DateSerial(Year(Me.txtFin) - 1, Month(Me.txtFin), Day(Me.txtFin) + 1)

15. For Each ctl In Me.Controls

16. If ctl.Name Like "txt####" Then ctl.Requery

17. Next ctl

18. End Sub

Commentaires du code

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

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

VI-B - La source de fPresentsADate

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

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

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

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.

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

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 :

Pour afficher le nombre dans chaque catégorie

VI-D - Code associé à fPresentsADate

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 :

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.