[AC-2007] Petit souci dans mes requêtes imbriquées

[Thony3351]

Bonjour à tous,

Je suis en train de gérer la traçabilité des stocks de vin pour un château, et il se trouve que je me retrouve face à un problème.

Donc voici déjà le MLDR



J'essaye de mettre au point un état qui permet de savoir, selon le "tiresBouchesMillesimes", le nombre total mis en bouteille, commandé, et sortieDiversetb.
Je me sers donc pour cela de mes tables "MiseEnBouteille", "LotCommandé" et "SortieDiverseseTb".

J'ai essayé cette requête là :

Code sql :

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
SELECT T.tiresbouchesmillesimes_code, requeteun.mise, requetedeux.sortie, requetetrois.commande
FROM    
  (SELECT t.tiresbouchesmillesimes_code, sum (miseenbouteille_nombredecols) AS mise 
FROM miseenbouteille meb, formatbouteille fb, famillevin fv, millesime m, tiresbouchesmillesimes t 
 WHERE m.millesime_annee = t.millesime_annee 
AND fv.famillevin_code = t.famillevin_code 
AND fb.formatbouteille_code = t.formatbouteille_code 
AND t.tiresbouchesmillesimes_code = meb.tiresbouchesmillesimes_code 
GROUP BY t.tiresbouchesmillesimes_code) AS requeteun,
 
      (SELECT t.tiresbouchesmillesimes_code , sum (sortiediversetb_nombredecols) AS sortie 
 FROM sortiediversetb meb, formatbouteille fb, famillevin fv, millesime m, tiresbouchesmillesimes t 
 WHERE m.millesime_annee = t.millesime_annee 
AND fv.famillevin_code = t.famillevin_code 
AND fb.formatbouteille_code = t.formatbouteille_code 
AND t.tiresbouchesmillesimes_code = meb.tiresbouchesmillesimes_code
 GROUP BY t.tiresbouchesmillesimes_code) AS requetedeux, 
 
     (SELECT t.tiresbouchesmillesimes_code , sum (lotcommandé_quantite) AS commande 
FROM lotcommandé lc, lot l,  formatbouteille fb, famillevin fv, millesime m, tiresbouchesmillesimes t 
WHERE m.millesime_annee = t.millesime_annee 
AND fv.famillevin_code = t.famillevin_code 
AND fb.formatbouteille_code = t.formatbouteille_code 
AND t.tiresbouchesmillesimes_code = l.tiresbouchesmillesimes_code 
AND lc.lot_code = l.lot_code 
GROUP BY t.tiresbouchesmillesimes_code) AS requetetrois ,tiresbouchesmillesimes t
 
WHERE requetedeux.tiresbouchesmillesimes_code = requeteun.tiresbouchesmillesimes_code
AND requetetrois.tiresbouchesmillesimes_code = requetedeux.tiresbouchesmillesimes_code
AND requeteun.tiresbouchesmillesimes_code = requetetrois.tiresbouchesmillesimes_code
AND t.tiresbouchesmillesimes_code = requeteun.tiresbouchesmillesimes_code

Cela marche très bien, les calculs sont justes, sauf que ça ne me donne les informations seulement pour les codes des tiresBouchesMillesimes qui sont enregistrés dans les trois tables à la fois, c'est-à-dire que par exemple, les tiresBouchesMillesimes qui sont seulement enregistrés dans la miseEnBouteille ET dans les "SortieDiverseTb" n'apparaissent pas.

Je tente donc de faire apparaître la liste de tous les tiresBouchesMillesimes, et, pour chacun d'entre eux, de faire apparaître le nombre total mis en bouteille, commandés et sortis(ce qui concerne la casse, le vol et autres...)

Cordialement,

Anthony

[Richard_35]

Bonjour Thony3351,

Envoyé par Thony3351

ça ne me donne les informations seulement pour les codes des tiresBouchesMillesimes qui sont enregistrés dans les trois tables à la fois

==> c'est normal, car tu fais les liens entre tes tables avec un WHERE puis des égalités.

Deux conseils :

• via l'assistant, créer des jonctions avec flèche à droite (LEFT JOIN) et laisser tomber les WHERE de jonction (glisser champ vers champ et lire l'explication Access en double-cliquant sur le trait de jonction) ;
• travailler par étape, donc segmenter tes requêtes et ne passer à la suivante que quand la précédente est OK.

[Thony3351]

Oui, je sais bien que c'est normal qu'il ne me sorte que celles qui sont liées, c'est pour cela que je demande de l'aide pour en faire avec des left outer join ou autres ..., choses sur lesquelles je n'ai quasiment pas d'expérience

[Richard_35]

Il faut donc créer des LEFT JOIN : liaison avec flèche à droite dans l'assistant. Cet assistant est très bien fait et beaucoup plus pratique à utiliser que d'écrire du SQL au kilomètre.
En outre, tu peux récupérer un code SQL juste avec la vision du code généré par l'assistant.

[Thony3351]

Je viens de terminer avec l'assistant, j'ai mes trois requêtes distinctes l'une de l'autre, je fais comment maintenant pour les assembler ? Et surtout pour commencer ma première ligne SQL qui les rassemble tous

[Richard_35]

Les requêtes, une fois crées, sont "vues" comme des tables, dans l'assistant.

Envoyé par Thony3351

.../... je fais comment maintenant pour les assembler ?

• tu crées une autre requête (via l'assistant) ;
• tu ajoutes tes trois requêtes ;
• tu les lies entres elles, avec flèche à droite (LEFT JOIN) via le champ qui va bien.

Attention à l'importance de la première table/requête du FROM : elle constitue la base des données à analyser.

[Thony3351]

Je te remercie de l'aide que tu m'apportes, j'avance grandement avec ça!
Il ne me reste plus qu'une petite chose à régler, peut-on faire des requêtes union avec l'assistant ou pas ?

[Richard_35]

Eh non... pour les requêtes UNION, il faut passer par le mode SQL et taper "bêtement" :

Code :

1
2
3
select * from t1
union
select * from t2

[Thony3351]

J'aurais juste une dernière petite question, j'utilise un filtre lors de l'ouverture d'un état, et je ne comprends pas quelle est la différence entre filtername et wherecondition ? J'ai une erreur qui me demande d'entrer une date en paramètre lors de l'exécution de l'ouverture de l'état alors que quand je tape la requête (autre que celle écrite plus haut) directement, cela fonctionne parfaitement.

Si quelqu'un pouvait m'éclairer sur ce point là.

Cordialement

[Richard_35]

Bonjour Thony3351,

Je te suggère d'ouvrir une nouvelle discussion dans la partie "Etat" : cela facilitera les recherches futures des utilisateurs de ce forum (un problème => une solution, c'est plus pratique à trouver !).

Si l'objet initial de cette discussion est, selon toi, résolu, n'oublies pas le flag (cela facilite, également, les recherches de solutions).

[Thony3351]

Il me reste un petit souci dans ma base, j'ai fait jusqu'ici toutes les manipulations expliquées, mais je me retrouve face à un problème que je n'arrive pas à résoudre :



Deux flèches convergent sur la table FormatBouteille, l'une opposée à l'autre, ce qui m'empêche de lancer la requête, et je ne sais pas comment faire, de plus, les requêtes que j'exécute mettent 10 secondes pour se lancer, est-ce normal ?

Cordialement,

Anthony

[Richard_35]

Bonjour Thony3351,

Plusieurs choses :

• tu ne sembles pas avoir coché l'intégrité référentielle entre FormatBouteille et TireBoucheMillesimes ;
• je te déconseille d'indiquer le sens des flèches dans les relations mais, plutôt, dans les requêtes (c'est peut-être ce que tu as fait) ;
• les flèches semblent mal orientées ;
• dans ta requête, tu utilises Millesime, FamilleVin et FormatBouteille, il n'est donc pas nécessaire de "ramener" toutes les tables de ta base : seules celles de la requête sont nécessaires (résoudra le temps de réponse, je pense).

Peux-tu modifier cela comme indiqué et reposter l'image de ta requête avec le code SQL généré ?

[Thony3351]

Toutes les tables qui sont affichées me sont utiles, car, ma requête m'indique les quantités totales expédiées, j'ai donc besoin de caisse_nombreDeCol * expedier_quantité.

Je ne vois pas comment on peut faire pour mettre le sens des flèches sans l'assistant, ne connaissant pas encore les left outer join etc...

L'intégrité référentielle y est, c'est juste que sur l'exemple je l'avais supprimée et remise

[Thony3351]

Au final ma requête donne ça :


SELECT FamilleVin.FamilleVin_Libelle, Millesime.Millesime_Annee, FormatBouteille.FormatBouteille_Libelle, Sum([expedier_quantite]*[caisse_nombredecol]) AS Expr1
FROM Millesime
INNER JOIN ((((FormatBouteille
INNER JOIN (Caisse
INNER JOIN Conditionnement ON Caisse.Caisse_Code = Conditionnement.Caisse_Code) ON FormatBouteille.FormatBouteille_Code = Caisse.FormatBouteille_Code)
INNER JOIN Lot ON Conditionnement.Conditionnement_Code = Lot.Conditionnement_Code)
INNER JOIN (LotCommandé
INNER JOIN expedier ON LotCommandé.LotCommandé_Code = expedier.lotcommandé_code) ON Lot.Lot_Code = LotCommandé.Lot_Code)
INNER JOIN (FamilleVin
INNER JOIN TiresBouchesMillesimes ON FamilleVin.FamilleVin_Code = TiresBouchesMillesimes.FamilleVin_Code) ON (TiresBouchesMillesimes.TiresBouchesMillesimes_Code = Lot.TiresBouchesMillesimes_Code)
AND (FormatBouteille.FormatBouteille_Code = TiresBouchesMillesimes.FormatBouteille_Code)) ON Millesime.Millesime_Annee = TiresBouchesMillesimes.Millesime_Annee
GROUP BY FamilleVin.FamilleVin_Libelle, Millesime.Millesime_Annee, FormatBouteille.FormatBouteille_Libelle

Mais ça ne me donne que les tirés bouchés millésimés présents dans les trois, que faut-il modifier dans le code sql pour que ça m'affiche tous les tiresbouchesmillesimes_code ?

[Richard_35]

Bien.

Le code ne correspond pas à l'image ! (Expr1 inexistant, que des INNER JOIN, etc...).

Les flèches sont mal orientées. Par exemple, la flèche devrait être à droite entre Conditionnement et Lot.

Ta table primaire est Millesime, donc la requête va partir de Millesime : tous les millésimes sont-ils bien présents ?

Crées une requête (vierge), via l'assistant :
- ajoutes la table Millesime (FROM) ;
- ajoutes la table TiresBouchesMillesimes (FROM) ;
==> postes l'image générée par l'assisant (sans rien toucher).

[Thony3351]

Effectivement, la requete ne correspond pas a l'image ; je m'explique.

En fait, il y a trois niveaux de requetes pour former ma requete finale que je désire, celle qui liste, par millésime, format et famille de vin, les quantités qui ont été :

• mises en bouteille
• commandées
• expédiées
• sorties pour diverses raisons

Donc à la base je crée quatre requetes pour chacune des quantités :

1. une requête avec tous les millésimes, toutes les familles de vin et tous les formats de bouteille, et les quantités mises en bouteilles correspondantes
2. une requête avec tous les millésimes, toutes les familles de vin et tous les formats de bouteille, et les quantités expédiées correspondantes
3. une requête avec tous les millésimes, toutes les familles de vin et tous les formats de bouteille, et les quantités sorties correspondantes
4. une requête avec tous les millésimes, toutes les familles de vin et tous les formats de bouteille, et les quantités commandées correspondantes

Sauf que :

Pour les quantités commandées et expédiées, je suis obligé, pour chacune des deux requêtes, de les former à partir de deux autres requêtes, car dans ma table lot, j'ai des lot qui sont simplement des bouteilles remplies et bouchées (qui n'ont donc pas de conditionnement, i-e étiquette, régie, caisse..) donc d'une part je fais le total des lot qui ont un conditionnement en faisant les liaisons entre les tables caisse, conditionnement, etc, et et je multiplie la quantité expédiée ou commandée (suivant ce que je veux) par le nombre de col par caisse, d'autre part, je fais le total avec les lots qui n'ont pas de conditionnement, sans lier aux tables caisse et conditionnement et sans multiplier vu qu'il n y a pas de caisse.

Ensuite, j'assemble ces deux requêtes pour n'avoir qu'un total par catégorie (commandes, expédition).

Et c'est ensuite là, une fois que ces deux requêtes sont réalisées et que les deux autres sont prêtes (mise en bouteille et sortie diverse) que je fais ma requête finale qui se construit à partir de ces quatre requêtes là.

Pour chacune des requetes que je fais, j'utilise les fleches afin d'avoir la liste telle quelle : (ex pour le total des mises en bouteilles)

• bouteille Château xxXXXxxx 2000 qté : 1200
• magnum Château xxXXXxxx 2000 qté : 500
• demi-bouteille Château xxXXXxxx 2000 qté : 0
• bouteille Château xxXXXxxx 1999 qté : 0
• demi-bouteille Château xxXXXxxx 1999 qté : 0
• magnum Château xxXXXxxx 1999 qté : 10
• impériale Château xxXXXxxx 2010 qté : 150
• balthazar Château xxXXXxxx 2010 qté : 1
• ...

C'est donc ma requête finale qui met 12 sec avant de se lancer ...Elle ressemble à ça :

• bouteille | Château xxXXXxxx | 2000 | Mise bouteille : 1200 | Sorties : 14 | Commandées : 500 | Expédiées : 350
• magnum | Château xxXXXxxx | 2000 | Mise bouteille : 1200 | Sorties : 3 | Commandées : 500 | Expédiées : 185
• demi-bouteille | Château xxXXXxxx | 2000 | Mise bouteille : 1000 | Sorties : 4 | Commandées : 500 | Expédiées : 255
• bouteille | Château xxXXXxxx | 1999 | Mise bouteille : 200 | Sorties : 14 | Commandées : 500 | Expédiées : 320
• demi-bouteille | Château xxXXXxxx | 1999 | Mise bouteille : 300 | Sorties : 6 | Commandées : 500 | Expédiées : 100
• magnum | Château xxXXXxxx | 1999 | Mise bouteille : 300 | Sorties : 14 | Commandées : 500 | Expédiées : 10
• impériale | Château xxXXXxxx | 2010 | Mise bouteille : 400 | Sorties : 1 | Commandées : 500 | Expédiées : 200
• balthazar | Château xxXXXxxx | 2010 | Mise bouteille : 500 | Sorties : 2 | Commandées : 500 | Expédiées : 300
• ...

Voici le MLDR de ma bdd :

[Richard_35]

Prenons les choses dans l'ordre.

Dans l'image que tu joins, la relation est correcte et sans flèche, donc c'est OK.
Dans l'image de ta précédente requête, il y a une flèche de TiresBouchesMillesimes vers Millesime, ce qui est incorrect : c'est l'inverse qui est correct.

C'est un exemple. Cela montre que c'est sur le sens des flèches qu'il faut travailler.

Peux-tu poster l'image et le SQL de ta 1ère requête ?

[Thony3351]

Oui, les flèches dans la première image, c'est moi qui les ai mises via l'assistant en cliquant sur la relation dans mes tables nécessaires à ma requête(ce n'est pas dans le MLDR mais dans l'ajout d'une requête).

Je mets ces flèches afin que la requête me donne la liste de toutes les valeurs y compris celles qui n'ont pas de données liées, et non la liste seulement qui ont des données liées (ce qui m'évite de taper directement le left outer join que je ne connais pas beaucoup)


Donc je mets les requêtes pour le total des commandes :

1° requête des lots avec conditionnement :

Code :

1
2
3
4
5
6
7
8
9
SELECT Millesime.Millesime_Annee, FamilleVin.FamilleVin_Libelle, Sum(nz((LotCommandé.LotCommandé_Quantite*caisse_nombredecol),0)) AS SommeDeLotCommandé_Quantite, FormatBouteille.FormatBouteille_Libelle
FROM (Millesime 
RIGHT JOIN (FormatBouteille 
RIGHT JOIN (FamilleVin 
RIGHT JOIN TiresBouchesMillesimes ON FamilleVin.FamilleVin_Code=TiresBouchesMillesimes.FamilleVin_Code) ON FormatBouteille.FormatBouteille_Code=TiresBouchesMillesimes.FormatBouteille_Code) ON Millesime.Millesime_Annee=TiresBouchesMillesimes.Millesime_Annee) 
LEFT JOIN (((Caisse RIGHT JOIN Conditionnement ON Caisse.Caisse_Code=Conditionnement.Caisse_Code) 
RIGHT JOIN Lot ON Conditionnement.Conditionnement_Code=Lot.Conditionnement_Code) 
LEFT JOIN LotCommandé ON Lot.Lot_Code=LotCommandé.Lot_Code) ON TiresBouchesMillesimes.TiresBouchesMillesimes_Code=Lot.TiresBouchesMillesimes_Code
GROUP BY Millesime.Millesime_Annee, FamilleVin.FamilleVin_Libelle, FormatBouteille.FormatBouteille_Libelle;

2° requête pour lots sans caisse :

Code :

1
2
3
4
5
6
7
8
9
SELECT Millesime.Millesime_Annee, FamilleVin.FamilleVin_Libelle, Sum(nz((LotCommandé.LotCommandé_Quantite),0)) AS SommeDeLotCommandé_Quantite, FormatBouteille.FormatBouteille_Libelle
FROM (Millesime 
RIGHT JOIN (FormatBouteille 
RIGHT JOIN (FamilleVin 
RIGHT JOIN TiresBouchesMillesimes ON FamilleVin.FamilleVin_Code = TiresBouchesMillesimes.FamilleVin_Code) ON FormatBouteille.FormatBouteille_Code = TiresBouchesMillesimes.FormatBouteille_Code) ON Millesime.Millesime_Annee = TiresBouchesMillesimes.Millesime_Annee)
 LEFT JOIN (Lot 
LEFT JOIN LotCommandé ON Lot.Lot_Code = LotCommandé.Lot_Code) ON TiresBouchesMillesimes.TiresBouchesMillesimes_Code = Lot.TiresBouchesMillesimes_Code
WHERE (((Lot.conditionnement_code) Is Null))
GROUP BY Millesime.Millesime_Annee, FamilleVin.FamilleVin_Libelle, FormatBouteille.FormatBouteille_Libelle;

3° requête : celle qui assemble les deux premières requêtes :

Code :

1
2
3
4
5
SELECT [Requête E/ Etat n°1 Commande1].Millesime_Annee, [Requête E/ Etat n°1 Commande1].FamilleVin_Libelle, ([Requête E/ Etat n°1 Commande1].SommeDeLotCommandé_Quantite+[Requête E/ Etat n°1 Commande2].SommeDeLotCommandé_Quantite) AS SommeDeSommeDeLotCommandé_Quantite, [Requête E/ Etat n°1 Commande1].FormatBouteille_Libelle
FROM [Requête E/ Etat n°1 Commande1] 
INNER JOIN [Requête E/ Etat n°1 Commande2] ON ([Requête E/ Etat n°1 Commande1].FamilleVin_Libelle = [Requête E/ Etat n°1 Commande2].FamilleVin_Libelle) 
AND ([Requête E/ Etat n°1 Commande1].Millesime_Annee = [Requête E/ Etat n°1 Commande2].Millesime_Annee) 
AND ([Requête E/ Etat n°1 Commande1].FormatBouteille_Libelle = [Requête E/ Etat n°1 Commande2].FormatBouteille_Libelle);

[Richard_35]

Je te suggère de supprimer la table Millesime dans tes requêtes "filles" et faire le regroupement par le champ (d'autant que la flèche est mal orientée) : cela optimisera l'ensemble.

La table Millesime devra être ajoutée dans la requête "mère" (finale), en premier avec flèche à droite, ce qui donnera NULL aux millésimes sans correspondance.

Tu n'as pas besoin, non plus, des tables de références, dans tes requêtes "filles", seule la clé est nécessaire : tu iras chercher le libellé dans ta requête "mère".

Pour ta première requête et dans cet ordre :
- ajoutes la table TiresBouchesMillesimes (FROM) ;
- ajoutes la table Lots (FROM) ;
- ajoutes la table LotCommandé (FROM) ;
- ajoutes la table Conditionnement (FROM) ;
- ajoutes la table Caisse (FROM) ;
- lies TiresBouchesMillesimes à Lots avec flèche à droite (LEFT JOIN) ;
- lies Lots à LotCommandé avec flèche à droite (LEFT JOIN) ;
- lies Lots à Conditionnement avec flèche à droite (LEFT JOIN) ;
- lies Conditionnement à Caisse avec flèche à droite (LEFT JOIN) ;
- regroupement par Millesime, FamilleVin_Code, FormatBouteille_Code (GROUP BY) ;
- Somme de LotCommandé_Quantite*caisse_nombredecol (SUM).
==> j'espère ne rien avoir oublié.

Ceci sans contrôle d'éventuelles erreurs de conception.

[Thony3351]

"Instruction SQL non exécutée : des jointures externes ambigües. Pour forcer