[Débutants]Analyse structure base de données simple

[Serge57]

[Cours] analyse de la structure d'une base de données simple
Ce cours se compose pour l'instant
- d'un vieux con, le professeur : papyturbo
- et d'un jeune con, l'élève : Serge57
Tout le monde peut participer si, étant débutant, quelque chose n'est pas clair : toutes les questions sont bienvenues.
Mais, débutant ou expert, merci de ne pas partir dans toutes les directions avec des questions hors sujet : elles seront déplacées dans le sujet prévu pour cela : [Cours papyturbo]Commentaires, remarques et suggestions
----------------------------------------------------------------
Objectif de ce cours :
Ce sujet (antérieurement nommé : Impossible de quitter Access) a commencé comme une question simple : comment réaliser une requête ?
Réponse sommaire (voir les premières réponses) : il faut revoir la structure de la base de données, les relations...
Nous allons donc prendre le temps qu'il faut pour
- explorer et comprendre les mécanismes de liaison entre les tables, c'est à dire la structure de la base de données,
- expliquer par l'exemple quelques termes de base comme jointures, relations et intégrité référentielle...
- le but est d'arriver à la même requête, mais sur une base "propre".
----------------------------------------------------------------
Conseil à tous les lecteurs : vous pouvez télécharger la base que Serge57 a mise en exemple dans la réponse #14, pour faire les exercices chez vous, en parallèle.
----------------------------------------------------------------
Le sujet d'origine débute ici :
----------------------------------------------------------------
Aprés l'execution du code VBA dans un module (sous access 2000 SP3), plus moyen de quitter access. La seule solution par le gestionnaire de taches Windows XP.
Voici la copie du code.
Principe : Remplir une Nouvelle Table avec des enregistrements d'autres tables triés par une requète.

 Sub SommeAvancementEtudePerFou()
   On Error GoTo Err_SommeAvancementEtudePerFou
    
    Dim NouvelleTable As New ADODB.Recordset
    Dim RequeteFournisseur As DAO.QueryDef
    Dim HSSF As DAO.Recordset
    Dim HSSP As DAO.Recordset
    
    Set RequeteFournisseur = CurrentDb.QueryDefs("HeureSectionSommeFournisseur")
    Set HSSF = RequeteFournisseur.OpenRecordset
    
    NouvelleTable.Open "Temp_AvancementEtudePerFou", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 
    ' Effacement des enregistrements de NouvelleTable
    NouvelleTable.Delete
 
    'Parcours de la RequeteFournisseur et ajoute à NouvelleTable
    Compteur = 0
    Do Until HSSF.EOF
        With NouvelleTable
            .AddNew Array(0, 1, 2), Array(HSSF(0), HSSF(1), HSSF(2))
            .Update
        End With
       HSSF.MoveNext
    Loop
   
Exit_SommeAvancementEtudePerFou:
    'Fermeture des recordsets
    NouvelleTable.Close
    HSSF.Close
    Set NouvelleTable = Nothing
    Set HSSF = Nothing
    Set RequeteFournisseur = Nothing
    Exit Sub
 
Err_SommeAvancementEtudePerFou:
    If Err = 3021 Then ' gestion de l'erreur "NouvelleTable" vide
        Resume Next
    End If
    MsgBox "Erreur sur la procédure SommeAvancementEtudePerFou"
    Resume Exit_SommeAvancementEtudePerFou

Est ce un problème dépassement de mémoire ?
De code non valide (tableau) ?

[Papy Turbo]

Bonjour,

2 choses me font tiquer :
- peut être c'est que pour le principe, mais j'éviterai soigneusement de mélanger ADO et DAO (je supprimerais une des 2 références, pour être sûr, et, si c'était moi, je ne garderais que DAO).
- effectivement, les Array() me font ??? (je sais pas quoi ? )

.AddNew Array(0, 1, 2), Array(HSSF(0), HSSF(1), HSSF(2))

Qu'est-ce que tu veux faire avec ces arrays ???

Et enfin, si le but de tout ça est de transférer le contenu de la requête 'HeureSectionSommeFournisseur' dans la table 'Temp_AvancementEtudePerFou', une simple exécution de requête ajout fera parfaitement l'affaire, en 2 ou 3 lignes de code.

[Serge57]

Bonjour PapyTurbo,

J'ai suivi ton conseil, je n'ai gardé que les référence en DAO. En faisant plusieurs essais, c'est bien les ARRAY qui me posent problème .... (bloque access). Pourquoi ???

Le but final du code est de transférer plusieurs requètes sur différentes tables, dans une même table. Est-il plus judicieux d'utiliser VBA ou SQL ???

Par contre pourriez-vous me donner une "méthode simple" pour vider tous les enregistrements d'une table.

[FreeAccess]

Bonjour,

Par contre pourriez-vous me donner une "méthode simple" pour vider tous les enregistrements d'une table.

En VBA, tu peux utiliser le code suivant :

DoCmd.RunSQL "Delete * FROM T_NomTable"

Bonne continuation
FreeAccess

[Papy Turbo]

Envoyé par Serge57

Bonjour PapyTurbo,

J'ai suivi ton conseil, je n'ai gardé que les référence en DAO. En faisant plusieurs essais, c'est bien les ARRAY qui me posent problème .... (bloque access). Pourquoi ???

parce qu'ils ne correspondent pas au type de paramètre attendu.
C'est pourquoi je t'ai demandé d'expliquer le détail de ce que tu veux faire.

Envoyé par Serge57

Le but final du code est de transférer plusieurs requètes sur différentes tables, dans une même table

Comprend pas. Pas à pas, s'il te plaît.

[Serge57]

j'explique ce que "j'essaye" de faire: j'ai 3 tables.
1ére table fournisseurs avec champs codefournisseurs,codeaffaire,codesection,heurespassées, et autres..
2ème table personnels avec champs codepersonnels, codeaffaire, codesection,heurespassées, et autres différent de ceux de la table fournisseurs...
3éme table heuresallouées avec champs codeaffaire, codesection, heuresallouées et autres ...
Le code personnel est sorti d'une table personnels, le code section d'une tables section, le code affaire est sorti d'une table affaire.
Le problème c'est de créer une 4éme table avec champs codeaffaire,codesection, sommedesheurespassées (tables personnels et fournisseurs) ,sommesdesheuresallouées (table heuresallouées)
par type d' affaires et type de section et d'affaire.
Voilà le pourquoi du code VBA qui me permet de faire des boucles imbriquées avec des if ..... et tout ce qu'il faudrait .....
Il y a peut être plus simple pour y arriver ???

[Papy Turbo]

Une requête devrait suffire puisque toutes les infos sont dans les diverses tables de la base.

Si je prends ton problème :

Le problème c'est de créer une 4éme table avec champs codeaffaire,codesection, sommedesheurespassées (tables personnels et fournisseurs) ,sommesdesheuresallouées (table heuresallouées)
par type d' affaires et type de section et d'affaire.

Je ne sais pas d'où tu tires les types d'affaires ni de section, mais tu devrais pouvoir faire un regroupement là dessus sans problème, si tu piges le principe.
Je commencerais par une requête basée
- sur la table Affaires, pour avoir la liste de tous les (Types d'affaire ?) + CodeAffaire, même si une affaire n'a aucune heure passée ou allouée (somme = 0, pour ces affaires là),
- le code section vient d'une table Sections, que je soupçonne d'être une subdivision des Affaires ?, donc faut ajouter cette table + la relier à la première, pour avoir une liste complète des (TypeAffaire+) CodeAffaire + (TypeSection+) CodeSection.
- manque plus que de relier la table personnels, la relier par CodeAffaire + CodeSection, et faire la Somme des HeuresPassées,
- idem avec les 2 autres tables Fournisseurs + HeuresAllouées.

Ta requête devrait alors afficher les champs
- (TypeAffaire ?) (regroupement)
- CodeAffaire (regroupement)
- (TypeSection ?) (regroupement)
- CodeSection (regroupement)
- Somme(Personnel.HeuresPassées) <-
- Somme(Fournisseurs.HeuresPassées) <- tu feras la somme de ces 2 là, dans un état par exemple, pour avoir le total des HeuresPassées
- Somme(HeuresAllouées)

[Serge57]

J'insiste (parce que je n'y arrive pas) et merci d'avance
Je joins un fichier .doc pour expliquer un peu plus les relations que j'ai entre les différentes tables. le Hic justement c'est que la table section n'a rien à voir avec les affaires, mais elle relie la table personnel, fournisseur et heures allouées à l'affaire pour les différentes sections. J'ai peut être mal organisé les tables?. En gros, j'ai une affaire, pour la réaliser je lui alloue un certain nombre d'heures pour différentes sections(pas toujours les mêmes). J'ai du personnel et des fournisseurs de différentes sections qui passent des heures sur cette affaires (je peux avoir des fournisseurs ou du personnels d'une section qui travail pour l'affaire alors que je n'ai pas allouée d'heures). Dans un état j'aimerai trouvé: une affaire, nombre heures allouées et passées par sections. Bien sûr il y à d'autres états qui me le nom des fournisseurs qui ont travaillées sur cette affaire.... J'essaye d'être le plus clair possible. Je sais que l'évolution de la discussion n'a plus rien à voir avec l' intilulé de la discussion, mais comme je tiens un interlocuteur compréhensif j'insiste....

[Papy Turbo]

OK, on va jusqu'au bout, sinon, c'est même pas drôle

D'abord, détail, mais il vaudrait mieux, à la place du document word, que tu mettres directement l'image .jpg (bouton 'Insérer une image') : on pourrait la lire en place, sans télécharger.

Ensuite, j'ai toujours du mal à lire les schémas de relation en "toile d'araignée", c. à d. avec des ralations qui vont aussi bien de gauche à droite que de droite à gauche. Je te propose de remettre les tables Fournisseur, SectionPerFou et Personnel sous la table Affaires, sur le bord gauche, de manière à ce qu'on lise bien les dépendances, toutes de gauche à droite.
Tant pis si c'est moins compact.

Ceci dit, en dehors du fait que la table Sections est indépendante de la table Affaires, la démarche ci-dessus est la même.

Dans l'interface de création de requêtes,
- tu ajoutes toutes les tables,
- access va les relier entre elles en respectant les relations de ton schéma,
- si tu veux ne voir que les affaires qui ont au moins une heure allouée ou passée, tu laisses tout comme ça. Sinon, si tu veux aussi voir les affaires avec 0 heures allouées/passées, tu remplaces les relations à partir de la table Affaires par des "Voir toutes les Affaires, mais seulement les..." (double clic sur la relation elle-même, pour ouvrir cette boîte de dialogue)
- tu choisis, dans l'ordre, les champs de chaque table à afficher dans l'état. Prends l'habitude de prendre chaque champ dans sa table d'origine, de préférence. Par exemple, le champ [codeaffaire] de la table Affaires -- on n'a pas besoin d'afficher les autres [codeaffaire]...
Donc, tous les champs de la table Affaires (codeaffaire, TitreAffaire...), puis, dans l'ordre, ceux de la table Sections (codesectionperfou, sectionperfou ?...), puis ceux qui nous intéressent : les heures allouées et passées de chaque table,
- tu cliques sur le bouton "sigma" dans la barre d'outils, pour afficher la ligne Opérations . Chaque champ, par défaut, aura "Regroupement" comme opération.
- tu indiques, sur cette ligne, que tu veux la Somme des [NbHeuresAllouées] et des diverses [Heures Passées]

Tu ouvres la requête, pour visualiser le résultat.
Tu dois avoir une ligne par affaire et par section, avec un total des heures allouées + heures passées personnel + heures passées fournisseurs.
Note bien les noms des champs de la requête ("SommeNbHeuresAllouées"...)

Dans ton état, tu feras
- un groupe par affaires,
puis, à l'intérieur,
- un groupe par sections,
- dans la section Détails, tu mettras la [SommeHeuresAllouées] + un seul contrôle pour totaliser les 2 sommes d'heures passées...

Et puis, tu fouilles à fond l'aide d'Access, pour en apprendre plus sur les requêtes et les états

[Serge57]

si tu veux ne voir que les affaires qui ont au moins une heure allouée ou passée, tu laisses tout comme ça. Sinon, si tu veux aussi voir les affaires avec 0 heures allouées/passées, tu remplaces les relations à partir de la table Affaires par des "Voir toutes les Affaires, mais seulement les..." (double clic sur la relation elle-même, pour ouvrir cette boîte de dialogue)

Voit le message que me donne la requete ? J'ai pourtant essayé de suivre l'aide pour corriger l'erreur, mais j'ai pas tout compris dans la méthodologie pour imbriquer 2 requetes. Donne moi s'il te plait une direction à suivre et ensuite je vais me battre jusqu'à ce je trouve (j'espère)...

[Papy Turbo]

Salut,

oui, ta requête est complexe !
Sans avoir les tables sous la main, ça va être difficile de faire des tests jusqu'à ce que tout soit au point.

D'abord, la disposition de tes tables dans cette requête me conviendrait parfaitement pour le schéma général des relations de la base (celui que tu as envoyé le 29)

Malheureusement, ça ne va pas convenir, à l'intérieur de la requête, où il faut régler les relations en question 1 par 1.

Je te propose de faire ce que j'aurais fait à ta place :
- commence une nouvelle requête avec la table Affaires seule.
Test : elle s'affiche bien, tout va bien.
- ajoute la table HeuresAllouees : là tu décides (et tu vérifies le résultat en ouvrant la requête) si tu veux "toutes les affaires" ou "seulement celles qui ont des heures allouées " ? Je pense que tu as choisi la 1ère solution (Left Join dans le SQL).
Tu vérifies le résultat...
- ensuite, tu essayes d'avoir les heures allouées par section, en ajoutant la table SectionPerFou.
Tu constateras que, pour certains choix sur la relation entre HeuresAllouées et SectionPerFou, tu auras le même message qui est affiché dans ton image jointe -> essaye chacune des 3 possibilités, et teste le résultat !
- continues comme ça, en ajoutant une par une les tables AvancementEtude, puis Personel et Fournisseur.
Tu devrais pouvoir créer ta requête sans avoir à créer de requête distincte (mais je ne garantis pas à 100%).
Le principe général, qui vaut pour Access et tout moteur de base de données est que tu ne peux pas inverser le sens des relations : le moteur va
- ouvrir une première table (Affaires, par exemple)
- en fonction de la relation avec la 2ème table, il va choisir de garder soit tous les enregistrements, soit seulement ceux qui ont aussi des heures...
- ensuite, il va ouvrir les sections (par exemple). Là, il ne peut pas prendre autre chose que les sections qui correspondent à chaque [Affaire/HeuresAllouées]. Tu n'as donc plus que 2 choix valides, et pas 3 !!!
Si là, tu lui dis "ouvre toutes les sections et seulement les heures allouées qui correspondent", il va avoir un grave problème de conflit avec les affaires, et t'afficher le message "jointures externes ambigües" !
Ce qui veut dire, rien qu'avec les 3 premières tables, tu vas probablement réussir en
- mettant la 1ère table (Affaires) à gauche,
- la 2ème (HeuresAllouées) à sa droite, avec une relation qui pointe de Affaires vers HeuresAllouées, (de gauche à droite)
- la 3ème, SectionPerFou, à droite de HeuresAllouées, avec une relation dans le même sens (de HeuresAllouées vers SectionPerFou) (continuons de gauche à droite, pour y voir clair...).
C'est logique : tu n'es interessé que par les sections qui correspondent à un enregistrement de la table HeuresAllouées (pour pouvoir afficher le nom de la section, et faire un regroupement dessus), et pas l'inverse.

Vas y lentement, pas à pas, avec moult tests, et ça va marcher.
Dis nous jusqu'où tu arrives bien, et à partir de quand ça coince (sauf si TVTB )

[Serge57]

me revoilà...

Vas y lentement, pas à pas, avec moult tests, et ça va marcher.

J'y suis allé très lentement et toutes les manières possibles, mais rien à faire
Le hic c'est que j'ai des affaires qui n'ont pas d'heure allouée (donc pas d'enregistrement dans la table "HeuresAllouées" alors qu'elles ont de l'avancement étude, ou inversement). La seule solution que je penses avoir trouvée c'est que dès que je fais un avancement etude, je crée un enregistrement dans le même section avec une valeur 0 dans la table "HeuresAllouées". Je suis en train de réflechir à la mise en forme de mes formulaires de remplissage de la table "AvancementEtudePersonnel" et "AvancementEtudeFournisseur.Je vais avancer sur ma mise en forme et je reviendrai avec "un autre intitulé" si j'ai d'autre problème. Enfin Merci pour ta patience et de ton aide "Papy Turbo". Je ne clos pas la discussion si tu as une idée meilleur ......

[Papy Turbo]

Quand tu dis

Le hic c'est que j'ai des affaires qui n'ont pas d'heure allouée (donc pas d'enregistrement dans la table "HeuresAllouées" alors qu'elles ont de l'avancement étude, ou inversement)

, ça ne doit pas être un hic.
Il faut que la relation (dans la requête) entre Affaires et HeuresAllouees soit : je veux toutes les affaires et seulement les HeuresAllouees qui correspondent.
Idem entre Affaires et les 2 autres tables d'heures.
Si tu revois le cours de Maxence Hubiche sur les jointures, ça s'appelle une jointure gauche (LEFT JOIN).
Bon, le seul truc qui m'ennuie, c'est, ensuite, comment placer la table Sections ? Normalement, encore des LEFT JOINs, depuis chaque table d'heures vers SectionPerFou...
Si tu peux créer un extrait de ta base avec juste ces 5 tables, le zipper et le coller ici, je le prendrai pour faire des essais dessus.
Si les données sont confidentielles, tu peux peut être vider cet échantillon et rajouter 3 ou 4 affaires bidon + 2 ou 3 sections, et juste quelques heures par ci, par là, histoire d'illustrer tous les cas possibles (1 affaire a des heures allouées mais rien de passé, ou le contraire, ou l'inverse, ou l'autre. )

[Serge57]

Ci joint un extrait des tables avec des enregsitrements "exemples".

Ce que j'ai voulu dire c'est que je ne peu pas faire une suite logigue de relations dans une requete.
J'ai essayé de mettre tous les cas de figure que je risques d'avoir dans les tables. Le nombre d'heures "c'est du n'importe quoi" mais c'est pour pouvoir faire un total.
La solution que je prévoyais

La seule solution que je penses avoir trouvée c'est que dès que je fais un avancement etude, je crée un enregistrement dans le même section avec une valeur 0 dans la table "HeuresAllouées".

n'est pas possible, trop compliqué à gérer pour les sections.

je rame...

Fichier attaché : SuiviAffaire 2006-06-04.zip (21 ko)

[Papy Turbo]

Bon, je commence par la bonne nouvelle, c'est possible.

et la solution est en pièce jointe, ci-dessous.

La mauvaise : la structure de tes tables pose de multiples problèmes.
Et il faut absolument la revoir de fond en comble, avant de te lancer dans la réalisation de l'application. Sinon, tu vas y passer des mois à tourner en rond.

Je note, en vrac :
- aucune intégrité référentielle. Faire un double clic sur chaque relation,dans la fenêtre des relations de la base, pour voir les options -> F1 pour étudier l'aide d'access
- des relations impossibles, entre une clé NumAuto (donc, un entier long) et une date !
- etc.
Dans la fenêtre des relations, j'ai
- supprimé l'affichage des tables annexes, pour simplifier, mais tu peux les réafficher en cliquant sur le bouton Afficher toutes les relations.
- remplacé tes relations "simples" par des relations d'intégrité, afin d'être sûr qu'aucune heure ne puisse être "allouée" ou "passée" sur une affaire ou une section qui n'existe pas ! (voir l'aide d'Access : intégrité)

Pour en revenir à ta fameuse requête, il aura fallu passer par
- 3 requêtes simples, qui listent, par affaire et par section, le nombre d'heures dans chaque catégorie (allouée, passée fournisseur + passée personnel)
- une requête Union qui regroupe les 3 en une seule,
- une requête finale qui refait la somme des heures, dans chaque catégorie.
Note qu'il a fallu, dans chacune requête de base, ajouter des champs "bidon", avec valeur = 0, pour que la requête UNION trouve bien tous les mêmes champs dans chacune des sous-requêtes de base.

Comme tu le vois, c'est très compliqué.

Et c'est révélateur, là aussi, d'une étude de la structure de la base qui n'a pas été poussée à fond avant de commencer.

Je ne peux pas, à distance, me substituer à toi ou aux utilisateurs de ton application, mais il me semble beaucoup plus que probable que la liaison entre les affaires et les sections doit être simplifiée.
Le plus simple, à première vue, serait de créer une table intermédiaire [SectionsParAffaire] qui contiendrait juste les 2 champs : codeaffaire + codesectionperfou.
Voir le tuto de Maxence Hubiche sur les relations (relations plusieurs à plusieurs).
Tu décideras alors, dans ton application, lors de la création d'une nouvelle affaire,
- soit de lui allouer automatiquement toutes les sections -> tu crées, pour cette nouvelle affaire, un enregistrement pour chaque section dans la table [SectionsParAffaire]
- soit, certaines affaires n'auront jamais de lien avec certaines sections : tu laisses l'utilisateur choisir parmi les sections celles qui conviennent...

Ensuite,
- les 3 tables d'heures ne seront plus liées par relation avec les tables affaires ni sectionperfou, mais uniquement avec celle là,
- et la requête que tu cherches à établir sera ultra-simple à créer.
À partir de cette fameuse table [SectionsParAffaire], tu n'auras plus qu'à totaliser les heures, sans aucune complication, ni message d'erreur de Jet.

D'autres simplifications apparaîtront certainement : tu pourrais très bien utiliser la table HeuresAllouées comme jointure entre les Affaires et les Sections (à la place de [SectionsParAffaires]).
Les 2 autres (Heures Passées) n'auront plus alors de relation qu'avec celle là.

Mais je ne vais pas te refaire ton application. Tu vois la direction ?