[AC-2007] Requete complexe et multiples tables

[funkidog33]

Bonjour à tous,

Je suis actuellement entrain de mettre en place une base de compétence dans mon entreprise.
Il y a plusieurs tables pour servir chaques specificités que voici:

• Table - Description
• opl - Contient les compétences (nom, numero, niveau )
• opl_diffusion - Lien entre les competences et multiples machines
• pts_diffusion - Les machines et leurs attribu (reference/nom etc.)
• operateur - Les employees
• operateur_opl - les lien entre les multiples employees et les "opl" qu'ils conaissent
• niveau - Niveau que chaque emplyee à sur une machine (facultatif, un employee peut connaitre une "opl" mais ne pas avoir de niveau donné sur la machine. On considère ici que le niveau d'un employee sur une machine est un objectif fixé par le chef d'équipe.

Donc au niveau des tables voici les champs (j'indique que els champs utile pour ce que je veux fair histoire d'alléger la lecture

• Table - champs
• opl - #id_opl, nom_opl, num_opl, niveau_opl
• opl_diffusion - #id_opld, &id_opl, &id_diffusion
• pts_diffusion - #id_diffusion, nom_diffusion
• operateurs - #id_op, nom_op
• operateur_opl - #id_opopl, &id_op, &id_opl, opl_a_reviser(true/false - permet de savoir que l'operateur conaissait une opl, mais doit la revoir.)
• niveau - #id_niveau, &id_diffusion, &id_op, niveau (de 0 à 4)

Voici le schema des relations entre chaque pour vous aider à mieux comprendre:



donc ce que j'ai besoin de faire

Le chef d'equipe aimerais pouvoir afficher dans une table le nom de l'opérateur, le nom de la machine, le niveau théorique qu'il a sur la machine (s'il existe) et le "niveau calculé" qu'il a sur cette machine.

Le "niveau théorique" est donc celui qu'on lit dans la table niveau, pour cela pas de soucis.
Le "niveaau calculé" est le niveau (de 0 à 4) qu'il a sur une machine en fonction des opl qu'il à appris (en fonction de ses compétences donc). Pour valider un niveau calculer sur une machine, l'operateur doit connaitre toutes les opl de cette machine et de ce niveau.
Ex : si l'opérateur connait toutes les opl niveau 0 de la machine X, il a donc un niveau calculé de 0 sur X
S'il manque une opl, il n'obtient pas ce niveau.


Je pensais donc faire une requete qui me permet tout d'abord de lister : nom de l'employee, nom machine, niveau de l'opl qu'il connais et le nombre total des opl de ce niveau sur cette machine.
Puis ajouter à coté une collone niveau totaux, qui pour le niveau en question, compte toutes les opl de ce niveau sur cette machine (donc un calcul ittératif ligne par ligne en fonction des valeurs de collones precedentes)

Voici don la première requete qui fonctionne :

Code :

1
2
3
4
SELECT operateurs.nom_op, pts_diffusion.nom_diffusion, opl.niveau_opl, Count(opl.id_opl) AS ["count_opl"]
FROM pts_diffusion INNER JOIN ((opl INNER JOIN (operateurs INNER JOIN operateur_opl ON operateurs.id_op = operateur_opl.id_op) ON opl.id_opl = operateur_opl.id_opl) INNER JOIN opl_diffusion ON opl.id_opl = opl_diffusion.id_opl) ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion
WHERE (((opl.id_statut_opl)=2) AND ((operateur_opl.opl_a_reviser)=False))
GROUP BY operateurs.nom_op, pts_diffusion.nom_diffusion, opl.niveau_opl;

(note: statut_opl c'est pour uniquement se baser sur els opl "actives" ... car yen a qui sont en archives et ne compte plus)

donc, c'est là que j'en suis, comment faire mon autre collone.
Ensuite une fois que j'ai mes deux collonnes je pense que je ferais un script VB qui lit champs par champs et ne conserve que ceux où l'opérateur à un niveauthéorique égale au niveau calculé ... une trouver une façon de présenter cela "viable" ...

Peut-être que je ne part pas dans le meilleur simple, je suis perdu au points où j'en suis et je m'en remet à vos conseil pour continuer.

Merci d'avance pour toute aide que vous m'apporterez.

[funkidog33]

personne ?

Ne vous inquietez pas, en attendant j'ai pu continuer à me creuser le crane et à coup d'imbrication de requète et avec quelques litres de café voici ce que j'ai à ce point.

J'a réussi à faire 2 requetes qui séparement produisent ce dont j'ai besoin.

1 - La permière compte le nombre d'opl de chaque niveau qu'un opérateur à sur chaque machine:

Code :

1
2
3
4
5
 
SELECT operateurs.nom_op, pts_diffusion.nom_diffusion, opl.niveau_opl, Count(opl.id_opl) AS ["count_opl"]
FROM pts_diffusion INNER JOIN ((opl INNER JOIN (operateurs INNER JOIN operateur_opl ON operateurs.id_op = operateur_opl.id_op) ON opl.id_opl = operateur_opl.id_opl) INNER JOIN opl_diffusion ON opl.id_opl = opl_diffusion.id_opl) ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion
WHERE (((opl.id_statut_opl)=2) AND ((operateur_opl.opl_a_reviser)=False))
GROUP BY operateurs.nom_op, pts_diffusion.nom_diffusion, opl.niveau_opl;

2 - La seconde compte le nombre d'OPL par niveau de chaque machine :

Code :

1
2
3
4
 
SELECT pts_diffusion2.nom_diffusion, opl2.niveau_opl, (SELECT COUNT (pts_diffusion.nom_diffusion) FROM  (opl INNER JOIN (pts_diffusion INNER JOIN opl_diffusion ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion) ON opl.id_opl = opl_diffusion.id_opl) WHERE ((pts_diffusion.id_diffusion=pts_diffusion2.id_diffusion) AND opl.niveau_opl = opl2.niveau_opl)  GROUP BY pts_diffusion.nom_diffusion, opl.niveau_opl) AS Expr2
FROM pts_diffusion AS pts_diffusion2 INNER JOIN (opl AS opl2 INNER JOIN opl_diffusion AS opl_diffusion2 ON opl2.id_opl=opl_diffusion2.id_opl) ON pts_diffusion2.id_diffusion=opl_diffusion2.id_diffusion
GROUP BY pts_diffusion2.nom_diffusion, pts_diffusion2.id_diffusion, opl2.niveau_opl;

A présent il me reste à trouver comment les mettre ensemble ... quelle est le plus éfficace
Sachant que le but final c'est d'avoir 4 collones : Nom de l'opérateur, no de la machine, niveau réel et niveau calculé.

C'est possible en SQL de faire des IF ?
Car si c'est posiblle, je serais tenté de faire une collone qui fait

Code :

"totale des opl en base" - "totale des opl de l'opérateur"

puis prendre le max niveau d'un opérateur sur une machine où ce calcul retourne 0 (donc egale - il à toutes les OPL )

Qu'en pensez-vous? Vais-je au plus efficace, il y a t'il une potentiel erreur?
Merci

[funkidog33]

si un modérateur passe dans le coin, peut-être ça serais mieux de placer ce topic sous la catégorie SQL du forum ? Car je pense pas que les fan de SQL viennent souvent faire un tour dans la section access ^^

[funkidog33]

Je pense faire une jointure entre les 2 requtes.
Je ne sais pas si c'est possible, dans tout les cas voici ce que j'ai fait ...
ça ne fait pas d'erreur, mais ça me demande ce qu'est "req_compt1" puis ça me retourne la table vide ... où est-ce que j'ai fait une erreur?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT req_compt1.nom_op, req_compt1.nom_diffusion, req_compt1.niveau_opl, req_compt1.count_opl, req_compt2.count_opl_total
FROM
(SELECT operateurs.nom_op, pts_diffusion.id_diffusion, pts_diffusion.nom_diffusion, opl.niveau_opl, Count(opl.id_opl) AS count_opl
FROM pts_diffusion INNER JOIN ((opl INNER JOIN (operateurs INNER JOIN operateur_opl ON operateurs.id_op = operateur_opl.id_op) ON opl.id_opl = operateur_opl.id_opl) INNER JOIN opl_diffusion ON opl.id_opl = opl_diffusion.id_opl) ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion
WHERE (((opl.id_statut_opl)=2) AND ((operateur_opl.opl_a_reviser)=False))
GROUP BY operateurs.nom_op, pts_diffusion.id_diffusion, pts_diffusion.nom_diffusion, opl.niveau_opl
)  AS req_compt1, 
(SELECT pts_diffusion2.id_diffusion, pts_diffusion2.nom_diffusion, opl2.niveau_opl, (SELECT COUNT (pts_diffusion.nom_diffusion) FROM  (opl INNER JOIN (pts_diffusion INNER JOIN opl_diffusion ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion) ON opl.id_opl = opl_diffusion.id_opl) WHERE ((pts_diffusion.id_diffusion=pts_diffusion2.id_diffusion) AND opl.niveau_opl = opl2.niveau_opl)  GROUP BY pts_diffusion.nom_diffusion, opl.niveau_opl) AS count_opl_total
FROM pts_diffusion AS pts_diffusion2 INNER JOIN (opl AS opl2 INNER JOIN opl_diffusion AS opl_diffusion2 ON opl2.id_opl=opl_diffusion2.id_opl) ON pts_diffusion2.id_diffusion=opl_diffusion2.id_diffusion
GROUP BY pts_diffusion2.nom_diffusion, pts_diffusion2.id_diffusion, opl2.niveau_opl
)  AS req_compt2
WHERE (((req_compt1.id_diffusion)=[req_compt2].[id_diffusion]) AND ((req_compt1.niveau_opl)=[req_compt2].[niveau_opl]))
ORDER BY req_compt1, req_compt1.nom_op;

merci

[funkidog33]

c'est bon ça marche!

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT req_compt1.id_op, req_compt1.nom_op, req_compt1.nom_diffusion as diffusion, req_compt1.niveau_opl as niveau, req_compt1.count_opl, req_compt2.count_opl_total
FROM
(SELECT operateurs.id_op, operateurs.nom_op, pts_diffusion.id_diffusion, pts_diffusion.nom_diffusion, opl.niveau_opl, Count(opl.id_opl) AS count_opl
FROM pts_diffusion INNER JOIN ((opl INNER JOIN (operateurs INNER JOIN operateur_opl ON operateurs.id_op = operateur_opl.id_op) ON opl.id_opl = operateur_opl.id_opl) INNER JOIN opl_diffusion ON opl.id_opl = opl_diffusion.id_opl) ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion
WHERE (((opl.id_statut_opl)=2) AND ((operateur_opl.opl_a_reviser)=False))
GROUP BY operateurs.id_op, operateurs.nom_op, pts_diffusion.id_diffusion, pts_diffusion.nom_diffusion, opl.niveau_opl
)  AS req_compt1 RIGHT OUTER JOIN
(SELECT pts_diffusion2.id_diffusion, pts_diffusion2.nom_diffusion, opl2.niveau_opl, (SELECT COUNT (pts_diffusion.nom_diffusion) FROM  (opl INNER JOIN (pts_diffusion INNER JOIN opl_diffusion ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion) ON opl.id_opl = opl_diffusion.id_opl) WHERE ((pts_diffusion.id_diffusion=pts_diffusion2.id_diffusion) AND (opl.niveau_opl = opl2.niveau_opl) AND (opl.id_statut_opl=2))  GROUP BY pts_diffusion.nom_diffusion, opl.niveau_opl) AS count_opl_total
FROM pts_diffusion AS pts_diffusion2 INNER JOIN (opl AS opl2 INNER JOIN opl_diffusion AS opl_diffusion2 ON opl2.id_opl=opl_diffusion2.id_opl) ON pts_diffusion2.id_diffusion=opl_diffusion2.id_diffusion
WHERE (((opl2.id_statut_opl)=2))
GROUP BY pts_diffusion2.nom_diffusion, pts_diffusion2.id_diffusion, opl2.niveau_opl
)  AS req_compt2 ON 
 (((req_compt1.id_diffusion)=[req_compt2].[id_diffusion]) AND ((req_compt1.niveau_opl)=[req_compt2].[niveau_opl]))
WHERE count_opl IS NOT NULL
GROUP BY req_compt1.id_op, req_compt1.nom_op, req_compt1.nom_diffusion, req_compt1.niveau_opl, req_compt1.count_opl, req_compt2.count_opl_total
ORDER BY  req_compt1.nom_op;

à présent me reste encore l'histoire des niveaux théoriques et calculés en fonction des résultats....

[funkidog33]

question:

(simple cette fois)

Si ça ça marche

Code :

SELECT * FROM Requete_niveau

Pourquoi ceci ne marche pas?

Code :

SELECT * FROM Requete_niveau WHERE Requete_niveau.niveau=2

(et il y a bien un champs niveau dans la requete "requete_niveau"

.... je pige pas.

[aieeeuuuuu]

bonjour

Envoyé par funkidog33

(et il y a bien un champs niveau dans la requete "requete_niveau"

.... je pige pas.

non, c'est un alias de colonne, qui n'est pas encore défini au moment de l'évaluation de la clause WHERE. utilisez le nom de la colonne initiale pour votre filtre

[funkidog33]

ma Requete_niveau étant celle du post n° 5

Cela devrais me faire donc

Code :

SELECT * FROM Requete_niveau WHERE req_compt1.niveau_opl=2

exact?
(ça ne marche pas.)
J'ai également essayé sans le "req_compt1" ça ne marche pas non plus

[aieeeuuuuu]

pourriez-vous poster la requête complète et indentée pour la rendre plus lisible ?

Postez également le message d'erreur

[funkidog33]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SELECT req_compt1.id_op, req_compt1.nom_op, req_compt1.nom_diffusion as diffusion, req_compt1.niveau_opl as niveau, req_compt1.count_opl, req_compt2.count_opl_total
	FROM
		(SELECT operateurs.id_op, operateurs.nom_op, pts_diffusion.id_diffusion, pts_diffusion.nom_diffusion, opl.niveau_opl, Count(opl.id_opl) AS count_opl
		FROM pts_diffusion INNER JOIN ((opl INNER JOIN (operateurs INNER JOIN operateur_opl ON operateurs.id_op = operateur_opl.id_op) ON opl.id_opl = operateur_opl.id_opl) INNER JOIN opl_diffusion ON opl.id_opl = opl_diffusion.id_opl) ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion
		WHERE (((opl.id_statut_opl)=2) AND ((operateur_opl.opl_a_reviser)=False))
		GROUP BY operateurs.id_op, operateurs.nom_op, pts_diffusion.id_diffusion, pts_diffusion.nom_diffusion, opl.niveau_opl)  AS req_compt1 RIGHT OUTER JOIN)
 
		(SELECT pts_diffusion2.id_diffusion, pts_diffusion2.nom_diffusion, opl2.niveau_opl, (
			SELECT COUNT (pts_diffusion.nom_diffusion) 
			FROM  (opl INNER JOIN (pts_diffusion INNER JOIN opl_diffusion ON pts_diffusion.id_diffusion = opl_diffusion.id_diffusion) ON opl.id_opl = opl_diffusion.id_opl) 
			WHERE ((pts_diffusion.id_diffusion=pts_diffusion2.id_diffusion) AND (opl.niveau_opl = opl2.niveau_opl) AND (opl.id_statut_opl=2))  
			GROUP BY pts_diffusion.nom_diffusion, opl.niveau_opl) AS count_opl_total
		FROM pts_diffusion AS pts_diffusion2 INNER JOIN (opl AS opl2 INNER JOIN opl_diffusion AS opl_diffusion2 ON opl2.id_opl=opl_diffusion2.id_opl) ON pts_diffusion2.id_diffusion=opl_diffusion2.id_diffusion
		WHERE (((opl2.id_statut_opl)=2))
		GROUP BY pts_diffusion2.nom_diffusion, pts_diffusion2.id_diffusion, opl2.niveau_opl
	)  AS req_compt2 ON
 
	 (((req_compt1.id_diffusion)=[req_compt2].[id_diffusion]) AND ((req_compt1.niveau_opl)=[req_compt2].[niveau_opl]))
 
	WHERE count_opl IS NOT NULL
	GROUP BY req_compt1.id_op, req_compt1.nom_op, req_compt1.nom_diffusion, req_compt1.niveau_opl, req_compt1.count_opl, req_compt2.count_opl_total
	ORDER BY  req_compt1.nom_op)

il n'y a aucun erreur.
La requete toute seule fonctionne parfaitement.
C'est uniquement quand je fait une requete sur cette requete en l'incluent par son nom (avec un condition) que ça ne me retourne aucun résultats.

Je pense que ça vient donc de la condition, mais pourquoi je ne sais pas ...