Discussion :

[Papy Turbo]

>>>> Merci de noter toutes remarques concernant ce cours dans le sujet parallèle : [Cours papyturbo]Commentaires, remarques et suggestions
-------------------------------------------------------------------------
Ce cours est la suite du [Cours pt-01][Débutants]Analyse structure base de données simple
Il commence donc par une réponse aux questions posées par Serge57, dans la réponse #38 du cours précédent.
-------------------------------------------------------------------------

Comment faire une addition de champ nulle avec d’autre ?? (dans la même requête)
J’ai trouvé un moyen (mais c’est lourd) en mettant un ‘VraiFaux’ dans les champs susceptibles d’avoir un champ nulle. Ne cherche pas j’ai tout supprimé dans la requête que j’ai envoyée (de peur de me faire tirer les oreilles).

Je te tirerais bien les oreilles, juste pour le fun, mais à propos de recherche en FAQ :
- cherche "Nul" dans la Faq de DVP, et tu trouveras Erreur d'exécution 94 : utilisation incorrecte de Null
- bien sûr, tu n'as pas ce n° d'erreur dans une requête, mais la réponse est la même : avec Nz([ChampQuiPeutEtreNul]) + Nz([AutreChamp]). Ce qui renverra 0, au lieu de la valeur Null, voir aide d'access (F1), sur la fonction Nz().

De toute façon, il y a d'autres problèmes à régler avant.

DONC Question ?? Vaudrait-il pas mieux de faire plusieurs requête ? mais comment ??
Joint ma table avec modification des relations et requête (pas complète) pour voir si je suis dans le bon chemin ?

Bon chemin ? je suis pas sûr. Surtout, tu vas trop vite, et tu ne vérifies pas tes résultats étape par étape.
Tu trouveras, en pièce jointe, la base du jour, renommée au 13 juillet.
Je n'ai pas modifié ta requête, sauf ajout de l'IdSectionsAffaires, pour contrôle, mais les totaux sont faux !
Pour vérifier cela, ce que j'ai déjà fait :
- ouvert ta requête SommeDifferenceHeures,
- supprimé les 2 tables d'heures passées, afin de me consacrer aux seules HeuresAllouees,
- enregistré la requête sous le nom 01-SommeHeuresAllouees,
- ajouté l'IdSectionAffaire, afin de pouvoir vérifier les nombre d'heures, pour chaque SectionAffaire, avec un tri par IdSectionAffaire,
- ouvert la requête et comparé les sommes d'heures avec le détail visible dans la table HeuresAllouees-SectionsAffaires,

---- aparté sur les listes déroulantes dans les champs de table ------------------------
Premier résultat (si tu fais cette opération avec ta base, celle qui est attachée dans le [cours pt-01], réponse #38) : tout faux !
Les n°s d'IdSectionsAffaires ne correspondent pas !
J'ai donc jeté un oeil sur la structure de la table HeuresAllouees-SectionsAffaires, et, pour le champ IdSectionsAffaires, dans l'onglet Liste de choix (en bas des propriétés, sous les champs) :
- tu as mis une liste déroulante, permettant de choisir une SectionAffaire en voyant le n° de clé de l'affaire + le n° de clé de section.
- l'idée est excellente, mais tu vois la confusion : lorsqu'on affiche le contenu de la table, c'est la clé de l'affaire qui s'affiche à la place de la clé de la SectionAffaire !
J'ai donc modifié la liste, pour qu'elle affiche toujours le n°IdSectionsAffaires dans la table, avec le n° d'affaire (plus intéressant que la clé) et le nom de la section visibles dans la liste, quand on la déroule.
Tu pourras y mettre les champs que tu veux, mais attention à ne pas remplacer l'affichage d'une clé par celui d'une autre !
V+ (À faire) : revoir toutes les listes déroulantes par champs
---- fin de l'aparté sur les listes déroulantes dans les champs de table ------------------------

Suite de la vérification des sommes d'heures allouées :

Comme tu peux le constater, on a bien
- 21 heures en SectionAffaire #4,
- 600 + 320 = 920, en SectionAffaire #5
- etc.
Jusque là, tout est bon.
Ensuite, comparaison entre la requête simple et la tienne, qui contient tout :

Voilà où je voulais en venir :
- les 2 requêtes sont triées par IdSectionsAffaires,
- les totaux ne sont pas les mêmes.
Avant de faire les additions/différences, il faut s'assurer que les sommes sont correctes. Faut se méfier de ce que renvoit SQL !
Il y a toujours une raison, même si elle n'est pas évidente.
Pour bien comprendre ce qu'il se passe, je te propose plusieurs directions :
- la requête qui ne contient qu'une seule table d'heures donne des résultats corrects, (on a vérifié)
- dès qu'on ajoute une 2ème table, les totaux d'heures allouées changent ? (à vérifier)
- si, dans SommeDifferenceHeures, on enlève la ligne d'opérations en cliquant sur le bouton "sigma", ou menu Affichages > Opérations, on voit le détail...
Le mieux à faire, pour bien comprendre ce point :
- reprendre le cours déjà cité : Comprendre les jointures / Relations dans Access de Maxence Hubiche, quitte à faire les mêmes exercices avec nos tables de SectionsAffaires et d'heures.
- il faut bien comprendre à quel moment Jet génère plusieurs enregistrements, avec les mêmes heures allouées ! (qui se retrouveront donc plusieurs fois dans le total)

Il faudra ensuite
- refaire 2 autres requêtes simples, chacune avec une seule table d'heures passées,
- vérifier les totaux en comparant avec la table concernée,
- vérifier que ces totaux aussi sont les mêmes dans ta requête.

Je ne te donne pas encore la solution, donc, n'hésite pas à poser toutes questions.

Question à Papy Turbo: Pour faire suivre mes fichiers 'zips' je suis obligé de supprimer les anciens fichiers attachées sur le cours. Celà pose-t-il un problème pour la suite ???

Ca ne semble poser aucun problème : j'ai pu télécharger aujourd'hui, aussi bien la nouvelle base que l'ancienne d'il y a quelques jours : elles sont toutes là.
Simple remarque : tu noteras que
- j'ai renommé ta base avec la date d'aujourd'hui, comme tu le fais à chaque fois
- j'inverse les dates : aaaa-mm-jj, pour que le tri, dans l'explorateur windows, soit correct et non pas en fonction du jour du mois !

[Serge57]

Je te tirerais bien les oreilles, juste pour le fun, mais à propos de recherche en FAQ :

J’ai répondu trop vite !!

Un peu lent pour la mise en forme des nouvelles requêtes, mais j’ai suivi ton conseil :

Le mieux à faire, pour bien comprendre ce point :
- reprendre le cours déjà cité : Comprendre les jointures / Relations dans Access de Maxence Hubiche, quitte à faire les mêmes exercices

Joint la table avec les requêtes (5 requêtes pour obtenir le résultat ?? cela me paraît lourd ?? mais faut-il se torturer l’esprit pour simplifier ?).
Le résultat désiré se trouve dans la requête N°5.

Hors sujet :
Je n’ai toujours pas trouvé comment coller une image par url, donc pour voir le résultat de ma requête il faut télécharger le zip.

-------
Fichier attaché : SuiviAffaire 2006-07-15.zip (35,0 ko)
-------

[Papy Turbo]

Hé ben, c'est bon.
On peut optimiser un poil, mais la différence de taille sur le disque ou de performances, entre ta requête et la version optimisée ci-dessous, sera à peine perceptible.
Par contre, j'espère que tu vois la différence entre la requête Union du mois dernier (voir [Cours pt01], réponse #15) et celle-ci.
Elle est plus simple, mais l'idéal serait de faire encore un test, avec 5 à 10 000 enregistrements ou plus, pour comparer les performances, comme le fait philben dans son Mythes et Réalités (voir, entre autres : [FAQ] Fermer tous les formulaires ouverts): seul un test peut montrer la différence.

------- Contrôle ----------------------
- Ce n'est pas que je ne te fais pas confiance, je ne me fais pas confiance, ni à Access, ni à SQL...
Donc, j'ai recontrôlé chaque résultat, comme indiqué + haut, réponse #1. Y compris, réaffiché les totaux d'heures passées par Employé/Fournisseur dans la requête finale, pour contrôle.

------- Présentation, classement ----------------------
- des commentaires (clic sur la requête + [Alt+Entrée], ou clic droit > Propriétés) indiquent les requêtes qui ne sont utilisées que pour le contrôle (débogage et tests, essais divers). La base pourra ainsi être nettoyée rapidement. La fenêtre de base de données doit être en mode Détails pour afficher les commentaires.

- la requête principale seule est entièrement visible. Les sous-requêtes sont masquées, toujours dans la fenêtre de propriétés. En mode "développeur", les objets masqués sont visibles grâce à la commande Outils > Options > onglet Affichage > case Objets masqués. Ca permet de distinguer au 1er coup d'oeil les sous-requêtes.
Si tu décoches cette case, il ne reste plus... que la seule requête utile pour les utilisateurs !

- chaque objet est numéroté. Certains trouvent cela trop lourd, mais c'est pour moi le seul moyen de m'affranchir de l'ordre alphabétique, pour
-- garder les sous-requêtes en dessous de la requête principale (hiérarchie des objets entre eux),
-- afficher l'ensemble de la liste de manière ordonnée (logique et non alphabétique). Il y a facilement plus de 1000 requêtes + sous-requêtes dans une application complexe..., donc bien réfléchir au plan de classement,
-- avant de renommer une requête, cocher les 2 cases : Outils > Options > onglet Général > cases Suivi information correction automatique + Effectuer correction automatique de noms. (à partir d'Access 2000).
Avec cette commande, lorsque tu renommes une sous-requête, Access ajustera son nom dans toutes les requêtes principales qui l'utilisent

------- Optimisation ----------------------
- 3 sous-requêtes seulement : toutes les opérations (addition/différence) pourront être faites directement dans la requête principale,
- dans chaque sous-requête, suppression de tout ce qui n'est pas strictement nécessaire : il ne reste qu'une seule table par sous-requête,
- opérations dans la requête principale,
- mise en forme du résultat final avec un format tricolore, (voir ci-dessous)
- changé un chiffre pour tester le résultat {Somme(Heures allouées) = somme(heures passées)}, en vert
- supprimé la ligne Opérations (Regroupement, etc.) devenue inutile dans la requête principale.

Bref rappel sur les formats de nombres (voir aussi l'aide pour dates, texte...) ------------
- les formats s'appliquent aussi bien sur des champs de tables ou requêtes, que dans les contrôles de formulaires,
- pour un champ de table, en mode création, le Format est dans l'onglet Général, en bas,
- pour un champ de requête, faire un clic droit sur le nom du champ > Propriétés, ou bien clic simple > Alt+Entrée pour afficher les propriétés du champ,
- les formats de nombres peuvent comporter de 1 à 4 parties, selon que le résultat est positif; négatif; zéro; null
- on peut utiliser les codes couleurs de la propriété Format ou de la fonction Format()(F1 > aide d'Access), en français dans l'interface QBE (QueryByExample = création de requêtes) d'une version française d'Access.

------- Conclusion ----------------------
Ta requête fonctionne , mais il est important de souligner :
- dès qu'une requête comporte des calculs, simples ou complexes, impérativement contrôler chaque calcul, un par un, dans l'ordre : la mise au point d'une opération de base peut entraîner la révision des autres !
Le temps passé sur les tests représente du temps gagné sur le débogage, toujours beaucoup plus long.
- si, dans la requête principale, le résultat d'une opération est faussé par les jointures multiples, faire l'opération dans une sous-requête simple, le vérifier, l'incorporer, vérifier à nouveau.
- par contre, si tu veux utiliser le résultat d'un calcul dans un autre calcul, comme tu as essayé de le faire avec ton 1er essai, il est obligatoire d'insérer le 1er dans une sous-requête.
En clair, si un champ, généralement un champ calculé, est nommé dans une requête, on ne peut pas utiliser ce nom pour effectuer un calcul dans la même requête. On peut utiliser ce nom dans une requête principale qui incorpore la 1ère.
- si tu as le temps, repasser pour supprimer tout ce qui n'est pas strictement nécessaire (tu peux encore supprimer l'affichage des champs calculés [HPEmployes] et [HPFournisseurs], si tu n'en as pas besoin )

------------------------------------------
À toi de voir pour la suite :
- soit on jette un oeil à la présentation du formulaire avec toutes ces heures allouées/passées, y compris celles que tu as ajoutées récemment !
- soit on passe directement à la récupération des données de la base existante dans cette nouvelle base : faudra séparer les tables de l'application, etc.

[Serge57]

À toi de voir pour la suite :
- soit on jette un oeil à la présentation du formulaire avec toutes ces heures allouées/passées, y compris celles que tu as ajoutées récemment !

Cela m’intéresse avant de passer à la récupération des données de l’ancienne base.

Suivant l’image du formulaire (SaisieAffaire) qui se trouve dans la réponse #34 du cours analyse structure base de données simple (bien sûr c’est l’ancien formulaire), je ne vois pas comment créer un enregistrement dans la table « SectionsAffaires » pour ajouter les heures allouées….
J’ai joint la base avec 2 formulaires SaisieAffaires-choix a lancer en premier.
Ce problème se posera aussi lors de la création du formulaire pour la saisie du pointage des employés et des fournisseurs. Une piste SVP.

Est-il préférable de créer 2 formulaires (un pour l’ajout et un autre pour la modification d’une affaire) ou 1 seul formulaire comme j'ai fait ?

Une fois se formulaire construit,

- soit on passe directement à la récupération des données de la base existante dans cette nouvelle base : faudra séparer les tables de l'application, etc.

on va rigoler

-------
Fichier attaché : SuiviAffaire 2006-07-17-1.zip (54,2 ko)
-------

[Papy Turbo]

Fin du [Cours pt-02][Débutants]Requête avec plusieurs sommes.

Suite dans le [Cours pt-03]turbo-formulaire (les bases)

[Serge57]

Juste avant de clore ce cours, j'ai une petite question d'organisation des requêtes dans une base.

Si une nouvelle requête pricipale a besoin d'une sous requête (mais que celle-ci existe déjà) est-il préférable de créer une nouvelle Sous-Requête ou d'utiliser celle existante ???

Merci d'avance ..

[Papy Turbo]

Si j'avais fait l'ENA, je commencerais ma réponse par "Votre question est particulièrement intéressante, cher monsieur..."
Mais on n'est pas sur France Inter.

Donc, sérieusement, je te conseillerai simplement de ne jamais, au grand jamais, réutiliser 2 fois la même requête.

Bien sûr, c'est tentant : pourquoi refaire une copie d'une requête existante, alors que,
- si on réutilise la même, 2 fois ou plus
et
- si, plus tard, on a besoin de la modifier,
il n'y aura qu'à modifier une seule fois cette requête "partagée", et le tour est joué. C'est à dire que tu gagneras dans les 2 à 5 minutes, ce jour là, plutôt que de refaire la même modif ailleurs, une 2ème fois.

Hélas, si je ne fais plus jamais ça, c'est suite à des heures perdues, plusieurs fois, dans des applications complexes.
Parce que j'avais modifié une requête utilisée, par exemple, dans un état (où je veux juste modifier la présentation de l'état, par exemple), et que je me suis aperçu (trop tard, c'est le client qui m'est tombé dessus ), qu'un formulaire basé sur la même requête était planté.
Ça paraît idiot, et bien simple de contrôler ça, mais quand tu as plusieurs centaines de requêtes, tu les vérifies toutes à chaque modif ?????? Impossible.
Et 2 à 3 mois + tard, tu ne sais plus quelles requêtes sont partagées ou pas.

Depuis, comme déjà mentionné + haut, je vais plus loin :
- il me paraît indispensable, quelle que soit la version d'Access, d'identifier clairement les dépendances.
- pour une requête, la dépendance est simple : qu'est-ce qui utilise cette requête ? sachant que je m'interdis de la réutiliser 2 fois !
- réponse : je numérote TOUT. Les tables (seulement s'il y en a beaucoup, plusieurs dizaines), les requêtes (toujours), les formulaires (toujours : besoin d'un n° pour les requêtes), les états (idem)...
En utilisant, au choix,
- une combinaison de lettres significatives : préfixe court (trigramme ? ) désignant un "domaine", l'application entière pouvant être découpée en domaines (CLIents, FOUrnisseurs, PROduits, ou autres...) Quoique je suis aussi allergique aux trigrammes, qui entraînent bien des confusions, et sont encore soumis à l'ordre alphabétique,
- des chiffres, permettant de trier tous objets par ordre logique : les sous-formulaires immédiatement au dessus ou en dessous de leur conteneur, dans l'ordre de leur utilisation sur le conteneur. Et, bien sûr, les sous-sous-formulaires idem. Idem pour les sous-états ou les sous-requêtes utilisées dans une requête "principale"...
- pour les requêtes seulement, un seul caractère précède cette numération :
- F, si la requête est utilisée dans un (sous-) formulaire, suivi du même n° d'indice (obligatoire) et, si possible du même nom complet que le formulaire qui l'utilise ou du contrôle sur ce formulaire.
- E, si la requête est utilisée dans un état.
- M, si elle est appelée par un module de code
- C, si elle est utilisée par une classe objet.
Un cas particulier, mais fréquent : une requête est appelée à partir du module de code (M ?) d'un formulaire (F !). Chacun son système, le mien, dans ce cas, c'est d'ajouter un "x" (pour requête eXécutée à partir de...), après le numéro du formulaire :
Exemple réel :


- toutes les requêtes ci-dessus "appartiennent" au Formulaire 30-001 (nom complet : [30-001 Stagiaires])
- 10, 20, 30, etc. sont mes "domaines" : je peux en insérer d'autres à tout moment, même au milieu, et je peux mettre 1000 formulaires ou états par domaine (généralement, ça suffit ).
Les requêtes qu'on voit servent soit,
- à des contrôles (listes déroulantes : lstNomsStagiaires, lstNoStagiaire, etc.)
- à des contrôles sur des sous-formulaires : sous-formulaire [30-001_a Demandes], par exemple
- les sous-requêtes sont, comme les sous-formulaires par rapport au form. principal, distinguées par rapport aux requêtes principales par un suffixe "_a", "_b" (je teste différents systèmes de sous-numérotation, d'une application à l'autre . Il m'est arrivé d'utiliser :1, puis :2... pour les sous-objets. C'est un poil + lisible avec ":" que ci-dessus, mais faut se méfier des ":" dans les noms d'objets)
- chaque contrôle peut avoir plusieurs requêtes, contenant des listes "+ ou - pleines" (liste complète, ou seulement les stagiaires d'un établissement, ou seulement ceux d'une composante d'un établissement, ou seulement d'un service ..., par ex.)
- les requêtes eXécutées à partir du code du formulaire 30-001 sont préfixées : F30-001 x1..., puis F30-001 x2... , etc.

Tu vois la taille de l'ascenseur, à droite de l'image ?
? currentdb.QueryDefs.Count renvoit 452. C'est une application moyenne. On dépase facilement les 1000 requêtes.

Je comprends que ça puisse faire peur à certains, mais la seule chose qui importe pour moi : avec un système comme celui-là, je ne peux pas me tromper quand à l'utilisation d'une requête.

Et ça, ça vaut des heures de gagnées, à moyen ou long terme.

[Maxence HUBICHE]

Donc, sérieusement, je te conseillerai simplement de ne jamais, au grand jamais, réutiliser 2 fois [B][COLOR=DarkRed]la même requête.

Très intéressant !

Mais, j'ai quelques questions relatives à ce qui est exprimé dans ce point (celui mentionné dans la quote ci-dessus).
Qu'en est-il des données calculées de base ?

Exemple.
Si je reprends la base comptoir (dans son concept, parce que la réalisation en français est plus que... moyenne)
J'ai des Commandes
Ces commandes regroupent des Produits dans la table des détails de commande
Dans cette table, on a les deux cliés étrangères N°Commande et Ref Produit, mais également une série de données de base comme le Prix Unitaire ou la Quantité ainsi, d'ailleurs que la Remise (%)
Ce qui est bien fait : il n'y a pas de résultat de calcul dans la table !
Mais, d'un autre côté, il est plus que probable que le total de la ligne serve à plusieurs reprises.
- Pour afficher, ligne par ligne le total dans un formulaire
- Pour servir de base au calcul du montant total de la commande
- Pour calculer le CA par client
- Pour estimer le CA généré par Produit
- Pour estimer le CA généré par Fournisseur
- ...

Donc
SI j'ai bien compris, tu préconises de faire ce même calcul, le calcul du montant de la ligne plusieurs fois ?
Mais, ce calcul ne changera jamais !
Ce sera toujours PU * QTE * (1- REM) !

D'où les questions suivantes :
Quel est donc l'intérêt de le refaire systématiquement ?
Ne serait-il pas plutôt intéressant, dans ce cas, de faire une seule requête, réutilisée par tous, comme une table de base, qui reprends tous les champs de Détails Commande, avec simplement en plus le champ calculé TotalLigne ?
S'agisse d'un cas particulier ?
Si c'est un cas particulier, quelle nomenclature utilises-tu ?

[Papy Turbo]

OK. Donc tu proposes de créer une requête, qu'on va baptiser une "pseudo-table" : elle remplacerait la table Détails de commande, en y ajoutant le Prix total de chaque ligne. Très tentant.

Pour répondre tout de suite, disons que, si j'utilisais cette méthode, je la mettrai en tête de liste des requêtes, avec un nom formé de
"_NomTable_SuffixeOpération".
Par exemple "_DétailsCommande_PrixNetParLigne".
La seule importance est de bien la distinguer de la table seule (ne pas l'appeler "_DétailsCommande" si la table s'appelle "DétailsCommande"), pour éviter de se tromper en examinant rapidement une requête qui utilise cette pseudo-table.
Attention aussi : on a toujours une floppée de requêtes en tête de liste : essais ratés, débogage, outils temporaires d'analyse, de test, etc. (voir les requêtes qui commencent par "----------" dans l'exemple de Serge !). Il ne faut pas que les pseudo-tables se noient là dedans.

1ère réaction : bien joué , ça pourrait être une exception. Ce qui démontre à ceux qui, débutants ou pas, gobent tout ce qu'on leur dit qu'il faut toujours se méfier : toute règle a toujours des exceptions !

Et puis, après quelque réflexions zardues, je dirai non.

3 raisons :
1- déjà exprimé ci-dessus :
- ça ne me coûte pratiquement rien de faire autant de fois "enregistrer sous..." pour créer une copie de cette requête pour chaque utilisation.
Le coût = quelques octets dans mon fichier .mdb (la taille du string SQL + le titre de la requête et quelques autres propriétés - négligeable).
Par contre, je suis sûr de pouvoir modifier chaque copie librement, sans jamais risquer de f... en l'air quoi que ce soit d'autre.
Ce n'est que mon expérience, mais c'est déjà suffisant (pour moi).
Dans la pseudo-table, pas question de supprimer un champ quelconque : les conséquences seraient très vite imprévisibles...

Dans le même registre, ce type de calcul ne changera pas, donc il n'y a quasiment aucun gain réel, au niveau de l'évolution de l'application.
Au pire, si la formule change (on ajoute une TVA par ligne ?), je la réécrirai 5 fois au lieu d'une, ou je referai 5 fois "Enregistrer sous...".

2- dans la réalité, il n'y aura pas un seul calcul, toujours le même.
Rien n'empêche d'afficher plus de détails, sur une facture par exemple :
- le produit
- prix unitaire
- quantité
- montant brut = PU * Qte
- la remise en %
- le montant de la remise = (PU * Qte) * Rem (ça fait toujours plaisir au client de voir ça en clair !)
- enfin, le montant net de la ligne = (PU * Qte) * (1-Rem)
Donc, comme pour les champs, tu dois mettre tous ces calculs dedans, et, au cas par cas, tu utilises tout, ou seulement une partie...
Bref, va y avoir du gâchis.

Et, dans tous les cas, tu ajoutes une surcouche entre la table de base et toute requête finale qui lui fait appel.

Dans + de 90% des cas, cette surcouche et ce surcoût (champs + opérations inutiles) sont négligeables. Mais...

3- Optimisation
Prenons le cas, assez fréquent après plusieurs années de fonctionnement, où un état met de 3 à 5 minutes pour s'ouvrir.
Les raisons :
- on a accumulé plusieurs dizaines ou 100aines de milliers de lignes (heures passées par + de 100 employés et/ou fournisseurs, à 200 jours/an...)
- on crée un état complexe, avec de nombreux critères de sélection, dont, entre autres, une fourchette de dates : 1 fois par mois, je veux le résumé du mois, par exemple.
- l'état appelle une requête1 qui contient plusieurs requêtes imbriquées :
--- requête1 fait une jointure entre requête2 et requête3
--- requête2 utilise requête4 et d'autres tables
--- requête3 fait une jointure entre requête5 et requête6, qui pourrait bien être notre pseudo-table, par exemple.

Si j'étais dans SQL Server, je disposerais pour cet ensemble de requêtes imbriquées d'un plan d'exécution. Ce plan est conçu par le moteur de SQL Server, optimisé par lui, et je peux l'optimiser encore.
Rien dans Access. Donc, le seul moyen d'optimiser, c'est de faire exécuter les sélections (critères de clause Where) les plus restrictives au niveau le + bas.
Explication :
La méthode normale et efficace dans + de 90% des cas consiste à :
- laisser l'utilisateur saisir tous les critères de choix (dont la fourchette de dates, etc.)
- les ramasser dans une belle "clause Where" (un string SQL partiel),
- passer ce string à l'état, avec la commande Docmd.OpenReport, dans le paramètre WhereClause.
- laisser Jet se dépatouiller avec son optimiseur pour faire au mieux.

Dans certains cas, il s'en sort remarquablement bien. On peut comparer le temps qu'il faut pour ouvrir l'état sans aucun critère (tables complètes), et avec des critères restrictifs : nettement plus rapide.
Mais ça ne marche pas toujours aussi bien, et on peut faire mieux.
Si il faut 5 minutes pour ouvrir l'état, c'est que Jet commence par
- créer les requêtes 5 et 6, en ouvrant en mémoire la totalité des tables concernées, (quand il ne peut pas optimiser)
- il crée ensuite la requête3, en faisant la jointure sur tous les (10 000 ou 100 000 ou ???) enregistrements,
- idem avec la 4, puis la 2
- et finalement la 1,
- enfin, il applique les critères de sélection sur la 1, c'est à dire qu'il jette à la poubelle la quasi-totalité des enregistrements lus et temporairement stockés en mémoire : la cata !
L'optimiseur peut faire mieux, si les noms des champs d'origine, etc. sont préservés... (on ne va pas faire un exposé là-dessus, je suis déjà beaucoup trop bavard ). Pas toujours.

Donc, pour le forcer à optimiser, on devra
- ouvrir les requêtes 4, 5 et 6 par DAO,
- y coller les critères les + restrictifs (par exemple notre fourchette de dates : si la 5 contient une table d'heures passées, elle ne contiendra plus que les heures du mois sélectionné )
- enregistrer ces requêtes sur le disque,
- ouvrir l'état avec DoCmd.OpenReport, mais sans aucune clause Where.
Résultat :
- les requêtes 5 et 6 sont toutes petites en RAM,
- les jointures en 3, puis en 1, s'exécutent en quelques centièmes de secondes,
- etc.
- l'état s'ouvre non pus en 5 minutes, mais en moins de 5 secondes, ordre de grandeur courant pour une bonne optimisation de requêtes complexes sur des tables lourdes.

Noter que cette technique s'applique aussi à une requête simple, si cette requête fait la jointure entre plusieurs tables :
- créer une sous-requête pour chaque table,
- appliquer les critères qui concernent chaque table dans la sous-requête,
- ...

Dans ce cas, tu vois la cata que représente la "pseudo-table" :
- non seulement, elle contient tous les enregistrements de la table Détails de commande, alors que je ne veux peut être afficher qu'une seule facture !
- en plus, elle contient tous les champs de la table, dont il faut stocker les valeurs en RAM, probablement pas tous utiles,
- et enfin, elle effectue, sur chaque ligne, tous les calculs possibles, alors que je n'en veux peut être qu'un seul !

En conclusion, je ferais ainsi :
- une pseudo-table, nommée comme ci-dessus pour la retrouver facilement.
- Avec un commentaire : "<< modèle de calcul des prix >>", par exemple.
- Elle sert pour les tests de début.
- Chaque fois que j'en ai besoin, je fais une copie complète, ou je ne copie que les formules de calcul voulues dans chaque requête qui en a besoin.

Et surtout, à l'attention des débutants : ne vous lancez surtout pas dans de telles optimisations si vous n'êtes pas parfaitement à l'aise avec les jointures dans les requêtes + les manipulations de requêtes par DAO.
Essayer d'optimiser une requête complexe "mal fagotée" (jointures dans le mauvais sens...) serait encore bien pire !
1- optimiser la structure des tables (Merise),
2- la structure et les jointures des requêtes,
3- les temps de réponse, si nécessaire.

[LostIN]

Papy Turbo

je suis comme toi, une requete utilisé qu'à un seul endroit. Ou alors des requetes écrites dans le code, comme ca, je suis sur de ne pas me planter.

Mais c'est vrai que du coup, ca devient parfois le bordel chez moi, qd il s'agit de requetes relativement basiques (d'où un nom similaire).
Peux tu expliquer de manière un peu plus prolixe ta codification?

Ou mieux mettre à dispo un exemple d'une appli?

[LostIN]

resalut

j'ai pris ta pièce jointe

question con ??
comment fais tu pour que le résultat de la requete soit coloré ??

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

0100__SommeDifferenceHeures

[LostIN]

c bon j'ai trouvé
je ne connaissais pas!

[Papy Turbo]

Peux tu expliquer de manière un peu plus prolixe ta codification?

Plus prolixe ? Moi qui me fais quotidiennement traiter de trop bavard ?
Non, je ne vois pas trop ce que je pourrais dire de plus.
La seule chose qui importe pour moi, c'est
1- grouper ensemble ce qui va ensemble : préfixe (alpha-)numérique et non pas ordre alphabétique,
2- savoir, essentiellement pour les innombrables requêtes, où elles sont utilisées :
- F pour un parent Formulaire, E pour un État,
- n° du formulaire ou de l'état (F30-001 dans l'exemple montré)
- nom du sous-formulaire (F30-001_a Demandes, pour le sous-formulaire "ssfDemandes" du formulaire 30-001, lui-même enregistré sous "30-001_a Demandes", sans le "F" bien sûr)
- nom du contrôle, si la requête est liée à une liste... (30-001_a Demandes - lstFiltreDomaines)
- autre chose, si nécessaire, pour l'identifier clairement (30-001_a Demandes - lstFiltreDomaines - All, pour une liste complète et 30-001_a Demandes - lstFiltreDomaines - Thème, pour une liste limitée aux seuls domaines appartenant au thème sélectionné ailleurs...)

L'essentiel : à chacun de ranger correctement ses classeurs et ses objets. Réfléchir, essayer, nettoyer... Si c'est pas parfait, ne recommence pas forcément tout, ça peut prendre des jours entiers sur une appli. complexe. La prochaine application, ou la prochaine version de celle là, seront mieux.

Ou mieux mettre à dispo un exemple d'une appli?

Pas de problème. Quel est ton budget ?
À condition que le client soit d'accord, et il va demander sa part, je peux t'envoyer des applications + ou - jeunes et + ou - complexes, disons, entre 5000 et 100000 € ?
P.S. : avec accès au code source, c'est un peu + cher.

Bon, je rigole.
Sérieusement, je crois surtout qu'en y passant 2 jours à fond, une fois, sur une appli quelconque, tu te seras fait un système solide, documenté (à peine + que ce qu'il y a là au-dessus, avec les détails particuliers à chaque appli) et clair. Et pas figé pour autant, bien sûr.
Ce n'est pas le système qui importe. C'est la mentalité. Si tu veux bosser propre, suffit de réfléchir, et de faire tes propres essais.

[Maxence HUBICHE]

Merci pour la réponse que j'ai reçue et qui dénote une grande maîtrise du produit, de ta nomenclature, et de l'optimisation.

Donc, ma réaction fût, la même :

1ère réaction : bien joué , ça pourrait être une exception. Ce qui démontre à ceux qui, débutants ou pas, gobent tout ce qu'on leur dit qu'il faut toujours se méfier : toute règle a toujours des exceptions !

Et puis, après quelque réflexions zardues, je dirai

que, pour reprendre les points, dans l'ordre (ce qui suit n'est que mon avis personnel, et n'engage, bien sûr, que moi)

1- Oui, c'est vrai que la charge de travail demandée pour 'copier-coller' la requête ne demanderait pas de temps énorme.
Je mets en face de cela que modifier la requête existante pour rajouter un calcul prendra encore moins de temps que de modifier une requête puis de la démultiplier (coupe à l'atout), que de retrouver la requête à copier dans cette dénomination ne me parait pas des plus simple s'il n'y a pas une requête type, qui ne sert donc à rien (belote), que retrouver les requêtes à mettre à jour pour mettre à jour les calculs sera plus long et source d'erreur car risque d'oubli (rebelote), que la maintenance globale s'en trouve plus ardue, ce qui me parait être l'antithèse d'une normalisation (dix de der)

2- C'est vrai qu'il peut y avoir plusieurs calculs.
Maintenant, si une requête n'utilise que quelques champs d'une autre requête, dans la mesure ou je me contente de faire du calcul utilisant les champs de base et non des champs déjà calculés, il n'y aura pas lieu de faire des calculs sur les champs non demandés.
Tu dis toi-même qu'il y a peu de chance que le calcul change.
Quel intérêt donc, de le refaire plusieurs fois ?
De plus, on est bien d'accord qu'en aucun cas, il ne convient de supprimer ou de modifier une requête tant qu'on n'en a pas suivi la totalité des références, car, dans le cas contraire, on risque de perdre des données.
Ce cas en est un exemple.
Mais la véracité de ce que tu exprimes au niveau de cette requête n'est-il pas valable pour TOUS les objets de base de données ?

3- Comme indiqué précédemment, je ne suis pas certain que les requêtes successives (je ne suis pas forcément pour, mais, des fois, c'est nécessaire) mettent en oeuvre la totalité des calculs intermédiaires pour arriver au résultat qui nous intéresse, sur la sélection des colonnes concernées.
J'ajouterai que, Access n'est pas non plus fait pour supporter un volume extraordinaire de transactions simultanées, ce qui fait qu'il est adapté si le nombre d'enregistrements ajoutés par jour reste raisonnable, pour un groupe de travail restreint. Donc, dans ce cas-là, il parait peu probable que ce niveau de performances soient attendues.
Mais l'argument est très intéressant.
Peut-être serait-il étonnant de faire des tests de performance sur les différentes solutions.



Encore une fois, merci pour la réponse.

[LostIN]

Plus prolixe ? Moi qui me fais quotidiennement traiter de trop bavard ?

Bavard, pas bavard, l'important est le contenu instructif, critique.

À condition que le client soit d'accord, et il va demander sa part, je peux t'envoyer des applications + ou - jeunes et + ou - complexes, disons, entre 5000 et 100000 € ?
P.S. : avec accès au code source, c'est un peu + cher.

Parfait, tu prends les cartes bleues? les tickets restau?


Merci pour ces infos, j'ai du me lancer dans une appli, et je me suis mis à numéroter tout ce qui traine.
Ca fait un peu barbare, mais au final, c'est très lisible. Et plus de risque de 'casser' qqch qui fonctionnait lors d'une mise à jour.

[Papy Turbo]

Je ne suis pas sûr d'avoir tout compris. Je vais donc essayer de voir point par point ?

1- Oui, c'est vrai que la charge de travail demandée pour 'copier-coller' la requête ne demanderait pas de temps énorme.
Je mets en face de cela que modifier la requête existante pour rajouter un calcul prendra encore moins de temps que de modifier une requête puis de la démultiplier (coupe à l'atout),

D'accord. Les 2 méthodes sont du même ordre, quelques minutes de plus pour la mienne.
La grosse cata, que je ne suis sûrement pas le seul à avoir connue, c'est : modifier une requête qui est utilisée plusieurs fois, sans avoir vérifié toutes ses dépendances.
Ça, ça peut coûter très cher.

que de retrouver la requête à copier dans cette dénomination ne me parait pas des plus simple s'il n'y a pas une requête type, qui ne sert donc à rien (belote),
que retrouver les requêtes à mettre à jour pour mettre à jour les calculs sera plus long et source d'erreur car risque d'oubli (rebelote),

Pas d'accord.
Nettement plus simple et rapide, avec un outil perso de type 'Qsearch()'. Cette recherche ne "fouille" que dans les propriétés SQL des requêtes.
La recherche des dépendances, elle aussi avec un outil perso, doit fouiller non seulement toutes les requêtes, mais aussi propriétés de tous objets (Form + contrôles + dans le code...) qui pourraient utiliser cette requête.
En tout cas avec Access 2000, que la majorité de mes utilisateurs ont toujours (dernières applis compatibles 2000 -> 2003).
C'est précisément parce qu'un tel outil prend plus d'une minute (ou 2 ou 3), en 2 étapes (collecte des propriétés, puis recherche) que je ne le ferai pas tourner souvent... et que je risque de me planter.

que la maintenance globale s'en trouve plus ardue, ce qui me parait être l'antithèse d'une normalisation (dix de der)

Chacun son point de vue, mon expérience dit "Tant pis pour la normalisation".
Quoique, ma numérotation est un peu une norme aussi, même si elle est perso. Mais chut ! faut pas le dire.

2- C'est vrai qu'il peut y avoir plusieurs calculs.
Maintenant, si une requête n'utilise que quelques champs d'une autre requête, dans la mesure ou je me contente de faire du calcul utilisant les champs de base et non des champs déjà calculés, il n'y aura pas lieu de faire des calculs sur les champs non demandés.

Pas tout compris, mais, je crois : d'accord que le ralentissement dû à des champs, calculés ou pas, qu'on n'utilise pas, paraît négligeable. Mais seulement au pif, au feeling. Seuls quelques vrais tests permettront de chiffrer. Disons que l'optimiseur de Jet est très bluffant, quelquefois

Tu dis toi-même qu'il y a peu de chance que le calcul change.
Quel intérêt donc, de le refaire plusieurs fois ?

Je ne l'exécute pas plusieurs fois : pas de ralentissement.
Je le recopie partout où il y en a besoin : ça coûte 0% de performance, et quelques octets (la chaîne SQL correspondante), dans 1 ou plusieurs requêtes.

De plus, on est bien d'accord qu'en aucun cas, il ne convient de supprimer ou de modifier une requête tant qu'on n'en a pas suivi la totalité des références, car, dans le cas contraire, on risque de perdre des données.
Ce cas en est un exemple.
Mais la véracité de ce que tu exprimes au niveau de cette requête n'est-il pas valable pour TOUS les objets de base de données ?

+ 1. D'où cette nomenclature : je ne me pose plus cette question des dépendances.
Tous les objets : oui, bien sûr.
Je ne réutilise donc jamais 2 fois le même sous-formulaire ou le même sous-état. J'en fais une copie, pour chaque usage.
Les requêtes sont les plus complexes à bien numéroter : on les utilise partout ! même dans le code VBA/DAO/ADO...
D'ailleur, la pluprt des "architectes de base de données" (SQL server, Oracle et autres clients/serveurs lourds) avec lesquels j'ai travaillé font la même chose : une vue par utilisation. Dans une grosse base, on se retrouve facilement avec des dizaines, voire, dans un ERP, avec des centaines de fois la même vue, sous des noms différents.
J'ai une exception , dans une appli récente :
- un sous-état qui ne contient qu'un seul champ memo, dans lequel je colle systématiquement, sous forme de texte lisible et à partir du code VBA, la liste des critères sélectionnés à l'écran par un utilisateur.
Ça marche, parce que l'utilisateur ne peut sortir qu'un seul état à la fois (sinon, cata totale !).
Tous les états de statistiques internes (36, au pif) utilisent le même sous-état pour afficher ces critères sur la première page de chaque état.
Il est signalé ultra-clairement dans la fenêtre BDD.
Il peut y avoir d'autres exceptions, mais...

3- Comme indiqué précédemment, je ne suis pas certain que les requêtes successives (je ne suis pas forcément pour, mais, des fois, c'est nécessaire) mettent en oeuvre la totalité des calculs intermédiaires pour arriver au résultat qui nous intéresse, sur la sélection des colonnes concernées.

"requêtes successives", tu veux dire "requêtes imbriquées" ?
Si oui, OK. Voir tests, pour bien vérifier si l'optimiseur de Jet les ignore ?

J'ajouterai que, Access n'est pas non plus fait pour supporter un volume extraordinaire de transactions simultanées, ce qui fait qu'il est adapté si le nombre d'enregistrements ajoutés par jour reste raisonnable, pour un groupe de travail restreint.

Là, on sort un peu du sujet.
Mais je ne vois pas le rapport entre
- nombre de transactions simultanées
et
- nombre d'enregistrements ajoutés par jour (= taille de chaque transaction, volume de saisie...)
J'ai des enquêtes médicales qui portent sur 50 à 60 000 personnes, avec des périodes de saisie, par 2 ou 3 utilisateurs maxi (base partagée sur petit réseau local du centre de recherche), de plusieurs milliers de fiches par jour.
Puis, plusieurs milliers à dizaines de milliers d'opérations (appel téléphone / mails / convocations au centre / visites à domicile / etc.), enregistrements générés par l'appli.
Pas de problème de volume.
Par contre, une statistique sur cette base quand elle est pleine, ça vaut sacrément le coup d'y passer une journée ou 2 pour optimiser.

Donc, dans ce cas-là, il parait peu probable que ce niveau de performances soient attendues.
Mais l'argument est très intéressant.
Peut-être serait-il étonnant de faire des tests de performance sur les différentes solutions.

Toujours d'ac. Manque juste le temps de faire tous ces tests...

[LostIN]

rien à voir avec le débat et les idées proposées que je trouve fortement interessantes.

Mais
Y a t'il un best-of des interventions de papy turbo ?


Merci en tout cas de partager vos connaissances

[Serge57]

une petite question

Si une nouvelle requête pricipale a besoin d'une sous requête (mais que celle-ci existe déjà) est-il préférable de créer une nouvelle Sous-Requête ou d'utiliser celle existante ???

amène un grand débat. (Commentaire juste pour vous montrer que je suis et que ça m'intéresse)

[Papy Turbo]

Un très très grand merci à Serge qui a servi de cobaye pour cette première expérience de cours "en direct", et bien sûr à tous les membres du forum et ceux de l'équipe Access qui ont apporté leur soutien et leurs commentaires, directement ou par MP.

Nous nous sommes amusés pendant près d'un an et demi, à survoler, sans aucun plan, les divers aspects de construction et mise au point d'une application de gestion type, avec base de données et interface.

Je vais maintenant, pendant les semaines/mois qui viennent, réexaminer tout ça pour voir ce qu'on peut concrètement en retirer. En gros, un exemple d'application type avec
- un formulaire type, (plein d'autres sont possibles)
- un début de bibliothèque pour stocker du code et des objets réutilisables,
- un journal d'erreur qui enregistre les erreurs non prévues,
- un moteur de mise à jour de la base, qui peut aussi servir de point de départ pour toute synchronisation ou autre import/export de données entre 2 bases relationnelles (.mdb ou autres).
- etc.

Tous commentaires sont bienvenus, essentiellement sur les méthodes de travail, bien sûr (pour les questions techniques, il reste le forum )

À plus...