Discussion :

[78chris]

Bonjour

Su une matrice standard c'est effectivement INDEX EQUIV mais sur un TCD

Code :

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

=SIERREUR(LIREDONNEESTABCROISDYNAMIQUE("CompteDeRef_Compo";MatriceTCD!$A$1;"Communs.Ref_Produit";A1;"Communs_1.Ref_Produit";A2);"")

J'ai prévu SIERREUR en cas d'erreur de frappe... Noms éventuellement à adapter si as renommé dans le requête...

[iliaseyah]

Bonjour 78chris,

J'ai adapté et ça fonctionne, merci! Je vais dupliquer cette formule afin d'avoir quelque chose qui ressemble à ça:

Pièce jointe 180584

Cela permettra d'avoir une interface assez intuitive pour la saisie des référence produits à comparer et une meilleure lisibilité, deux possibilités:

- Soit via un formulaire de saisie, on pourra entrées les ref produits à comparer et elles seront mises en forme dans le tableau ci-dessus
- Soit les ref produits seront téléchargées depuis un fichier excel nommé "planning"

Voici mon fichier dans sa version la plus avancée: http://www.cjoint.com/c/EFphRvWW6dM

Merci de m'avoir appris à pêcher !

[iliaseyah]

Bonjour,

Je reprend ta réponse, car je travaille de plus en plus sur les requêtes SQL Access. Et j'essaye de comprendre ta requête qui fait appel à de quelques clauses intéressantes.
Peux-tu me détailler en quelques phrases le détail de ta requête ("ce qu'elle fait..")?

Surtout le "LEFT JOIN", "AS", "GROUP BY" et "HAVING" .... Je sais à peu près ce qu'elles font chacune d'entre elles séparément mais j'ai du mal à comprendre leurs fonctions lorsqu'elles sont utilisé dans la même requête.

Merci d'avance!

Code :

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

1
2
3
4
SELECT Communs.Ref_Produit, Communs_1.Ref_Produit, Count(Communs_1.Ref_Compo) AS CompteDeRef_Compo
FROM Communs LEFT JOIN Communs AS Communs_1 ON Communs.Ref_Compo = Communs_1.Ref_Compo
GROUP BY Communs.Ref_Produit, Communs_1.Ref_Produit
HAVING (((Communs_1.Ref_Produit)<>[Communs]![Ref_Produit]));

[Invité]

Bonjour,
le HAVING s’effectue sur le group by par exemple Count(*)>2 c'est le group by qui exécute le count.
nous utiliseront les where pour filtrer les données bruts de la base et en plus ça nous allège le traitement du group by car les données son déjà filtrées par le" where.

dans ta requete, je ne voie pas l'intérêt du group by car tu ne fais aucune statistique (Count;Max;Min) je subodore qu'il s'agit de dédoublonnage alors un select distinct sufit!

Code :

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

1
2
3
ql="SELECT  distinct Communs.Ref_Produit, Communs_1.Ref_Produit, Count(Communs_1.Ref_Compo) AS CompteDeRef_Compo  " & _ 
"FROM Communs LEFT JOIN Communs AS Communs_1 ON Communs.Ref_Compo = Communs_1.Ref_Compo  " & _
"where(((Communs_1.Ref_Produit)<>[Communs]![Ref_Produit]));"

les requête type
select champ1,champ2,count(champ2)
from table1 inner join table2 on table1.id=table2.id
where champ1=quoi
group by champ1,champ2
having count(champ2)>10

from table1 inner join table2 tous les valeur de la table1 ce trouvant dans la table2

from table1 left join table2 tous les valeur de la table1 ce trouvant ou pas dans la table2

from table1 Right join table2 tous les valeur de la table2 ce trouvant ou pas dans la table1

Edit:effectivement tu fais un count

Code :

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

1
2
3
4
SELECT Communs.Ref_Produit, Communs_1.Ref_Produit, Count(Communs_1.Ref_Compo) AS CompteDeRef_Compo
FROM Communs LEFT JOIN Communs AS Communs_1 ON Communs.Ref_Compo = Communs_1.Ref_Compo
WHERE(((Communs_1.Ref_Produit)<>[Communs]![Ref_Produit]))
GROUP BY Communs.Ref_Produit, Communs_1.Ref_Produit;

[78chris]

Bonjour

Dans Access, il est généralement plus simple d'utiliser le QBE (Query by example) et son interface graphique que le SQL un peu lourding en syntaxe (par rapport à celui d'Oracle).

Je t'ai donné le code SQL car c'était plus facile pour toi qui n'a jamais fait de requête et qu'il s'agissait d'une requête de niveau un peu avancé.

Ici l'objectif est de compter les composants communs entre Produits d'une même table : d'où le placement 2 fois de cette table, l'orientation de la requête pour avoir au moins une fois chaque produit et la condition pour éviter de comparer le produit à lui-même.

Dès que l'on compte (ou autre opération statistique) il faut regrouper pour donner la "maille" du calcul (c'est un peu comme dans les TCD, le calcul dépend des champs mis en ligne ou colonne qui donnent la même notion de regroupement, en plus souple du fait des croisements possibles).

Voila, ceci pour compléter les excellentes explications de rdurupt et replacer dans le contexte.

[iliaseyah]

Bonjour,

Merci pour ta réponse,

Réponse très claire, structurée. Cela m'aide beaucoup, une fois de plus 78chris apporte une contribution non-négligeable dans le développement de mon apprentissage.

[iliaseyah]

Bonjour messieurs,

Je remets d'actualité cette discussion, en effet nous avions parlé il y a deux mois d'une problématique:
Soumettre des requêtes à une base de données Access et les mettre en forme dans des TCD via Excel.

Je me tourne à nouveau vers vous, car je suis un peu coincé dans mon développement,

Rappelez-vous, nous avions créés des requêtes de sélection. C'est ce que j'aimerais effectué à nouveau, les données d'entrées sont plus ou moins les mêmes mais le résultat final est légèrement différent.
Explication:

Petit rappel d'une table de ma BDD:



Typiquement sur l'image ci-dessus on voit clairement que chaque produit est lié à un certains nombre de référence composant qui le composent, jusqu'ici tout va bien, rien de nouveau.
J'aimerais avec votre aide, trouver une requête de sélection avec un OU logique permettant de comparer deux produits mais pas n'importe lesquelles seulement les produits qui ont plusieurs faces, exemple:
423820F1 & 423820F2
C'est le produit 423820, mais il a deux faces F1 et F2, et le but est de comparer ces deux faces. Cf ci-dessous



Sur l'image ci-dessus on peut voir le résultat souhaité dans total général, mais le top serait d'avoir une nouvelle matrice qui traite tout les produits de la Matrice Composition de mon fichier calculs des communs.
et pour chaque comparaison créé une nouvelle ligne nommé 423820 et faisant le OU logique de 423820F1 & 423820F2.

L’intérêt est de ne plus tenir compte des faces et d'avoir un résultat global pour le produit, ainsi ça me permettrai de connaitre la composition total d'un produit.

Dossier de fichiers: http://www.cjoint.com/c/EHshCcqgioM
(obligé de passer par Cjoint, le gestionnaire de pièces jointe a du mal.)

N.B.:OR : <Expr1> OR <Expr2> ==> ou inclusif, au moins une des deux expressions doit être vérifiée (et/ou).

[78chris]

Bonjour

Crée la requête suivante

Code :

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

1
2
SELECT Communs.*, IIf(Mid([ref_produit],Len([ref_produit])-1,1)="F",Left([ref_produit],Len([ref_produit])-2),[ref_produit]) AS Racine
FROM Communs;

et utilise, dans ton TCD basé sur cette requête, Racine avec ou sans ref_produit selon que tu veux pouvoir globaliser ou détailler.

[iliaseyah]

Bonjour 78chris,

Merci beaucoup de t'impliquer dans ma résolution de problème.
j'ai testé ta requête et elle fonctionne bien.
Malheureusement je me suis aperçu que certains produits ne respecte pas la syntaxe standard, par conséquent peut-on généralisé ta requête à TOUS les produits.
Voici un exemple:



On peut voir au lieu d'avoir Nom_Du_ProduitF1 ou Nom_Du_ProduitF2, on a Nom_Du_ProduitE1/E2 ou encore Nom_Du_ProduitD1/D2.
Comment parer à ce genre d'éventualité ?

[iliaseyah]

J'ai essayais de modifier la requête pour qu'elle réponde à cette nouvelle contrainte:

Code :

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

1
2
3
 
SELECT Communs.*, IIf(Mid([ref_produit],Len([ref_produit])-1,1)="F" or IIf(Mid([ref_produit],Len([ref_produit])-1,1)="D" or IIf(Mid([ref_produit],Len([ref_produit])-1,1)="E",Left([ref_produit],Len([ref_produit])-2),[ref_produit]) AS Racine
FROM Communs;

Est ce que je peux procédé comme cela ?

[78chris]

Bonjour

3 requêtes chaînées :

• Couples0

  Code :

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

  SELECT DISTINCT Communs.Ref_Produit, Left([communs]![ref_produit],Len([communs]![ref_produit])-1) AS Racine0 FROM Communs;

• Couples

  Code :

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

  SELECT DISTINCTROW Couples0.Ref_Produit, Couples0.Racine0 FROM Couples0 INNER JOIN Couples0 AS CommunsRacine2_1 ON Couples0.Racine0 = CommunsRacine2_1.Racine0 HERE (((Couples0.Ref_Produit)<>[CommunsRacine2_1]![ref_produit]));

• A utiliser pour l'analyse TCD

  Code :

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

  SELECT Communs.*, Nz([Couples]![Racine0],[Communs]![REf_produit]) AS Racine FROM Communs LEFT JOIN Couples ON Communs.Ref_Produit = Couples.Ref_Produit;

Je les ai appelé couples mais cela marche pour plus de 2 normalement.

Je n'ai pas réussi à faire en 1 requête ni 2 pour ma part.

[iliaseyah]

Je ne comprend pas très bien comment tu t'y prend, peux-tu m'expliquer un peu plus dans le détail stp?

Est ce que ces requête sont capables de gérer la contrainte expliquer dans mon message précédent ?

Autrement dis pour ces produits:
900300E1/E2
900810D1/D2
574431TB (Dans ce cas il suffit simplement de supprimer "TB")
N.B.: TB qui signifie Tom-Bottom, en électronique une carte Top-Bottom est une carte qui a strictement les mêmes composants sur sa face recto que sur sa face verso.
906300B1/B2

Ce qu'il y a en gras ce sont les indices à supprimer, sans oublier de "fusionner" avant. La seule particularité c'est le produit xxxxTB, pas besoin de fusionner. Il suffit pour ce type produit de supprimer l'indice TB.

La liste des "cas" que l'on peut rencontrer ci-dessus est exhaustive, seule la racine change ... 900300,900810,...

Concernant tes requête, pour la création de mon TCD je ne peux m'appuyer que sur une requête pour faciliter l'actualisation des données.. Comme dans la version précédente je vais chercher mes infos dans la colonne "racine", ce qui me donne :


La matrice est très correcte, ce qui sous entends que la requête qui l'alimente est bien conçue. Merci 78chris.

D'où ma question:

Code :

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

SELECT Communs.*, Nz([Couples]![Racine0],[Communs]![REf_produit]) AS Racine FROM Communs LEFT JOIN Couples ON Communs.Ref_Produit = Couples.Ref_Produit;

Est ce que cette requête est une synthèse des deux autres (c'est ce que je crois avoir compris) ?

[78chris]

Re

Oui on ne peut faire en une requête. Donc pour obtenir la dernière qui couple en leur associant une racine, tous les ref_produits qui ont le même code si ont fait abstraction du dernier caractère, il faut prémâcher le boulot avec les 2 premières requêtes.

Seule le dernière est utilisée dans excel : elle invoquera elle-même les 2 autres.

Reste le cas possible ou tu aurais 2 ref produit ne différant que sur le dernièr caractère mais où ce caractère ne représente pas un chiffre. Il faudrait alors compliquer un peu pour analyser le dernier caractère.

A noter aussi que tu as des F1 sans F2. Donc pour une gestion vraiment saine il vaudrait mieux avoir la notion de face dans tes données sources car ce type de découpage est toujours un peu limite.

Je n'ai pas saisis pour TB ce que tu espères... Ou, peut-être, traiter les codes se terminant par TB et leur faire une racine sur le code moins les 3 derniers caractères ?

Edit : : vérification faite, Excel bloque sur la fonction NZ. Donc le dernière requête doit être

Code :

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

1
2
SELECT Communs.*, IIf([Couples]![Racine0] Is Null,[Communs]![REf_produit],[Couples]![Racine0]) AS Racine
FROM Communs LEFT JOIN Couples ON Communs.Ref_Produit = Couples.Ref_Produit;

[iliaseyah]

Re,

Compris pour le "goupillage" des requête entre elles.

Reste le cas possible ou tu aurais 2 ref produit ne différant que sur le dernièr caractère mais où ce caractère ne représente pas un chiffre. Il faudrait alors compliquer un peu pour analyser le dernier caractère.

Je ne comprend pas très bien ou tu veux en venir, peux-tu me donner des exemples pour que je puisse visualiser la problématique?

Je n'ai pas saisis pour TB ce que tu espères... Ou, peut-être, traiter les codes se terminant par TB et leur faire une racine sur le code moins les 3 derniers caractères ?

Oui c'est de cette manière qu'il faut procéder, par contre pourquoi moins les 3 derniers ? Moins les 2 derniers ("TB") suffit non ?

Edit : : vérification faite, Excel bloque sur la fonction NZ. Donc le dernière requête doit être

N.B.:Vous pouvez utiliser la fonction Nz pour renvoyer zéro, une chaîne nulle (" ") ou une autre valeur spécifiée lorsqu’une type de données Variant a la valeur Null. Vous pouvez, par exemple, utiliser cette fonction pour convertir une valeur Null en une autre valeur et l’empêcher de se propager dans une expression.

Je me trompe surement, mais comment se fait-il qu'Excel bloque cette fonction, sachant que le code SQL dont on parle est compilé par Access, non ?

A noter aussi que tu as des F1 sans F2. Donc pour une gestion vraiment saine il vaudrait mieux avoir la notion de face dans tes données sources car ce type de découpage est toujours un peu limite.

Dans le cas où l'on a qu'une face il faut procéder comme pour le cas "TB" supprimer le suffixe et préserver la racine comme "nouveau nom de produit" dans la requête de selection.

Pour info:
L'idée est de créer un TCD constitué de l'ensemble des ref_produits et de leurs compositions respectives. A la différence de MatricComposition, ici on ne veut pas tenir compte des faces. Autrement dit dans MatricComposition nous avions la composition des produits mais par face donc nous avions pour certains produits deux compositions pour le même produit (exemple: 423820F1 & 423820F2 /// au lieu de 423820). Idéalement et logiquement pour un traitement global par produit il faudrait n'avoir qu'une et même composition par produit en fusionnant les deux faces.

---------------------------------------------------------------------------------------------------------------------------------------
Recap:

Cette première requête est utilisé pour traiter une première fois les ref_produits :

Code :

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

SELECT DISTINCT Communs.Ref_Produit, Left([communs]![ref_produit],Len([communs]![ref_produit])-1) AS Racine0 FROM Communs;

Le second traitement est effectué par celle-ci:

Code :

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

SELECT DISTINCTROW Couples0.Ref_Produit, Couples0.Racine0 FROM Couples0 INNER JOIN Couples0 AS CommunsRacine2_1 ON Couples0.Racine0 = CommunsRacine2_1.Racine0 HERE (((Couples0.Ref_Produit)<>[CommunsRacine2_1]![ref_produit]));

Et pour terminer, cette dernière requête synthétise les deux précédentes, pour faciliter un traitement aval avec un TCD sous Excel:

Code :

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

1
2
SELECT Communs.*, IIf([Couples]![Racine0] Is Null,[Communs]![REf_produit],[Couples]![Racine0]) AS Racine
FROM Communs LEFT JOIN Couples ON Communs.Ref_Produit = Couples.Ref_Produit;

Tu confirmes ?

[78chris]

RE

Oui c'est bien les 3 requêtes à chaîner.

Il y a toujours eu des fonctions Access non acceptées par Excel : je ne sais trop qui gère quoi dans leurs échanges... Pour autant que je sache c'est un module Windows car il fallait l'installer sur les anciens Windows.


Reste le cas possible ou tu aurais 2 ref produit ne différant que sur le dernièr caractère mais où ce caractère ne représente pas un chiffre. Il faudrait alors compliquer un peu pour analyser le dernier caractère.
Je ne comprend pas très bien ou tu veux en venir, peux-tu me donner des exemples pour que je puisse visualiser la problématique? : exempe ABCDZ et ABCDY seraient 2 produits différents mais de verraient attribuer une même racine.


Je n'ai pas saisis pour TB ce que tu espères... Ou, peut-être, traiter les codes se terminant par TB et leur faire une racine sur le code moins les 3 derniers caractères ?
Oui c'est de cette manière qu'il faut procéder, par contre pourquoi moins les 3 derniers ? Moins les 2 derniers ("TB") suffit non ? Dans ton exemple tu as indiqué 574431TB mais il y a aussi 574432TB donc la racine commune semble 57443.

Pour les faces ce que je voulais dire c'est que le code de la face ne devrait pas être intégré dans la référence : une bonne BD aurait une reféfence (en gros la racine) et un autre champ Face avec 1 ou 2 ou... mais j'imagine que l'amont ne peut être modifié...

[iliaseyah]

RE

Ok pour la liaison Excel, Access !

Reste le cas possible ou tu aurais 2 ref produit ne différant que sur le dernier caractère mais où ce caractère ne représente pas un chiffre. Il faudrait alors compliquer un peu pour analyser le dernier caractère.
Je ne comprend pas très bien ou tu veux en venir, peux-tu me donner des exemples pour que je puisse visualiser la problématique? : exempe ABCDZ et ABCDY seraient 2 produits différents mais de verraient attribuer une même racine.

Alors dans ce cas un test conditionnel ne permettrait pas de différencier le cas d'un produit représenté de deux manière différente (cas des faces) ET de deux produits vraiment distinct.

Je n'ai pas saisis pour TB ce que tu espères... Ou, peut-être, traiter les codes se terminant par TB et leur faire une racine sur le code moins les 3 derniers caractères ?
Oui c'est de cette manière qu'il faut procéder, par contre pourquoi moins les 3 derniers ? Moins les 2 derniers ("TB") suffit non ? Dans ton exemple tu as indiqué 574431TB mais il y a aussi 574432TB donc la racine commune semble 57443.

Non non, attention, 574431TB et 574432TB sont bien deux produits différents leurs racines respectives sont 574431 et 574432. Leurs particularité c'est seulement qu'ils sont tous les deux Top-Bottom.

Pour les faces ce que je voulais dire c'est que le code de la face ne devrait pas être intégré dans la référence : une bonne BD aurait une reféfence (en gros la racine) et un autre champ Face avec 1 ou 2 ou... mais j'imagine que l'amont ne peut être modifié...

Oui dans l'idéal tu as raison, on devrait avoir ça! Mais tu as aussi raison, aucune modification possible sur le procédé d'importation qui est effectué en amont !

[78chris]

Re

S'il n'y a pas a synthétiser 574431 et 574431TB rien à faire de particulier...

[iliaseyah]

Rien de particulier non, à part supprimer le "TB" pour n'avoir qu'une racine, plus propre pour les analyses aval.

[78chris]

Re

Si tu veux couper F ou autre lettre de face et TB alors modifier
la 1ère requête

Code :

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

SELECT DISTINCT Communs.Ref_Produit, Left([communs]![ref_produit],Len([communs]![ref_produit])-2) AS Racine0 FROM Communs;

le 3ème requête

Code :

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

SELECT Communs.*, IIf(Right([Communs]![ref_produit],2)="TB",Left([Communs]![Ref_produit],Len([Communs]![ref_produit])-2),IIf([Couples]![Racine0] Is Null,[Communs]![Ref_produit],[Couples]![Racine0])) AS Racine FROM Communs LEFT JOIN Couples ON Communs.Ref_Produit = Couples.Ref_Produit;

[iliaseyah]

Je pense que tu n'as pas bien compris, si on est dans le cas d'un TB, il faut "juste" supprimer le suffixe. Pour tous les autres cas (faces "F" ) en plus de se séparer du suffixe il faut également fusionner les deux sous-produits.