Discussion :

[py86acces]

Bonsoir a tous
j'ai besoin de votre lumiere
j'ai deux tables
la premiere est ma liste de mes produits avec les champs suivant.

IDmat NONmat DESIGNATIONmat UNITmat
1 x xxxxx u
2 a aaaaa m
3 f fffff kg


la deuxieme sert pour la mis a jour des prix des produit avec les champs suivant.

IDprix IDmat DATEde mise a jour PRIX
1 1 20/06/15 10.00
2 1 01/07/15 15.00

Ma question est la suivante
J'ai un formulaire avec ma liste complète
je voudrais prendre pour chaque produit le dernier prix
dois je faire une requete ou passer par code dans mon formulaire.

Pouvez vous me diriger vers la donne FAQ
Merci pour le coup de main

[fsmrel]

Bonsoir py86acces,


Partons des deux tables suivantes :

Table matos

idmat    nomat    desmat    unitmat
    1    x        xxxxx     u
    2    a        aaaaa     m
    3    f        fffff     kg
    4    s        sssss     u

Table prixmatos

Idprix    idmat    datemaj      prix
     1        1    20/06/2015     10
     2        1    01/07/2015     15
     3        2    10/06/2015     17
     4        2    01/07/2015     20
     5        4    01/07/2015     40

dois je faire une requête ou passer par code dans mon formulaire.

Je dirais que peu importe, le tout est de faire en sorte qu’ACCESS exécute la requête suivante :

SELECT matos.idmat, max(prixmatos.prix)
FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat
GROUP BY matos.idmat ;

Ou celle-ci, au cas où des matériels pourraient ne pas avoir de prix (cas de idmat = 3) :

SELECT matos.idmat, Nz(max(prixmatos.prix),'/')
FROM   matos LEFT JOIN prixmatos ON matos.idmat = prixmatos.idmat
GROUP BY matos.idmat ;

[py86acces]

Bonsoir et merci pour ta réponse
je vois cela se soir et....

A LA PROCHAINE

[py86acces]

OK
cela fonctionne pour avoir prix le plus haut
Mais je veux recuperer le prix avec la date la plus recente.
g vu sur des forum qu'il fallait utiliser la fonction
max(date)
mais malgré tous mes essais je ne vois vois pas ou inserer ou comment lier dans le code

[fsmrel]

Pour avoir à la fois le prix le plus élevé et la date la plus récente :

SELECT matos.idmat, Nz(Max(prixmatos.prix),'/') AS PrixMax, Nz(max(prixmatos.datemaj),'/') AS MaxDate
FROM matos LEFT JOIN prixmatos ON matos.idmat = prixmatos.idmat
GROUP BY matos.idmat;

[py86acces]

BONSOIR
OK
JE SUIS DANS LE FLOU LE PLUS TOTAL , VOICI UN FICHIER AVEC DEUX TABLES SIMPLIFIE
JE VOUDRAI SI CELA EST POSSIBLE POUR MA REQUETE OBTENIR LE RESULTAT SUIVANT

TABLE PROD ( PRODUIT )

IDpro pro
1 a
2 b
3 c

TABLE PRIX ( PRIX PRODUIT )

IDprix pro date prix
1 a 01/01/15 5.00€
2 a 02/01/15 10.00€
3 a 03/01/15 1.00€
4 b 01/01/15 2.00€
5 c ' '



requete 1

PROD PRIX DATE
a 1.00€ 03/01/15
b 2.00€ 01/01/15
c ' '


JE M'EXCUSE PAR AVANT CAR JE PENSE QUE JE ME SUIS MAL EXPRIME
DEBUTANT

Encore merci pour votre compréhension

[fsmrel]

Bonsoir py86acces,


En fait, pour chaque produit, vous voulez connaître le prix le plus récent. C'est cela ? Si ou, essayez ceci :

SELECT prixmatos.idmat, prixmatos.datemaj, prixmatos.prix
FROM   prixmatos 
INNER JOIN 
(
 SELECT matos.idmat, max(prixmatos.datemaj) AS datemax 
 FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
 GROUP BY matos.idmat
)  AS t 
ON prixmatos.idmat = t.idmat AND prixmatos.datemaj = t.datemax ;

C’est la jointure de deux tables, dont l’une est connue : prixmatos (qui contient les prix), et la 2e, nommée t est dynamique, elle est le résultat de l’opération qui récupère la date la plus récente par produit :

 SELECT matos.idmat, max(prixmatos.datemaj) AS datemax 
 FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
 GROUP BY matos.idmat ;

La table dynamique contient deux colonnes : idmat et datemax (on peut donner n’importe quel nom).

[py86acces]

Bonsoir
cela fonctionne

pour le principe de la deuxième table aie aie ......
besoin de temp pour comprendre
je suis preneur d'une faq ou tuto concernant ce principe

Merci a toi
bonne soirée

[py86acces]

Re bonsoir et désolé

concernant la requète je viens de m apercevoir que les produits n'ayant pas de prix n’apparaisse pas.
est ce qu il y a un moyen de mettre 0.00€


merci

[fsmrel]

Bonsoir py86acces,


Parlons du mariage...

Je traduis ce qu’a écrit Hugh Darwen en 1987, et qui fut repris en tant que contribution darwenienne (signée du pseudonyme anagrammique Andrew Warden) dans l’ouvrage de Chris Date Relational Database, Writings 1985-1989, paru en 1990 :

« Étudions maintenant l’opérateur Jointure naturelle et voyons comment il nous change la vie. Je le représente selon la syntaxe suivante :

MARRIAGE (table-expression-1, table-expression-2)

On peut voir ainsi la merveilleuse union accomplie par cet opérateur :

Dans un premier temps, il y a les tendres préliminaires à l’occasion desquels les colonnes des deux opérandes sont examinées, afin de voir lesquelles portent le même nom. Celles-ci sont appelées « colonnes communes » quand, outre le même nom, elles sont du même domaine.

Dans un deuxième temps a lieu l’accouplement : à cette occasion les lignes compatibles des deux opérandes sont jointes (à savoir les lignes qui ont la même valeur pour les colonnes communes).

Il y a enfin, l’orgasme grandiose quand un ensemble de colonnes communes est éjaculé.

Le fruit de cette union est un individu aussi parfait qu’on peut le souhaiter, ayant hérité des caractéristiques de ses parents. »

Autrement dit, de même que l’union de deux ensembles E1 et E2 produit un ensemble E3, suite au mariage de deux tables T1 et T2 naît une table T3 (et pas une chaise !), de même nature que ses parents, donc pouvant à son tour se marier et plus généralement participer à toute opération de l’algèbre relationnelle : on est dans un monde relationnellement clos. Attention quand même avec SQL, coupable d’autoriser la production de sacs (bags), incompatibles avec l’algèbre relationnelle (lignes en double, colonnes sans nom ou en double, présence de Null...)


Reprenons la requête :

 SELECT matos.idmat, max(prixmatos.datemaj) AS datemax 
 FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
 GROUP BY matos.idmat ;

Le résultat est une table, appelons-la T, comportant deux colonnes, idmat et datemax. Elle contient une ligne par idmat ayant répondu présent lors de la jointure :

T    idmat    datemax
     -----    ---------- 
         1    01/07/2015 
         2    01/07/2015 
         4    01/07/2015 

Le produit pour lequel idmat = 3 ne figure pas parce qu’absent de la table prixmatos. Par ailleurs, puisqu’on a utilisé la fonction MAX, pour idmat = 1, SQL a retenu la date du 01/07/2015, alors que si on avait utilisé la fonction MIN, c’est la date du 20/06/2015 qui aurait été retenue.

Il s’agit maintenant de compléter ce résultat, afin de faire figurer le prix du produit. En présupposant l’existence de la table T, il suffirait d’exécuter la requête :

SELECT prixmatos.idmat, prixmatos.datemaj, prixmatos.prix 
FROM   prixmatos INNER JOIN T ON prixmatos.idmat = T.idmat AND prixmatos.datemaj = T.datemax ;

Mais hélas ! la table T est purement virtuelle...

Heureusement, en 1987 (dans l’ouvrage que j’ai mentionné tout au début), Andrew Warden lança le défi suivant (et dont l’origine remonte au langage ISBL, vieux de 40 ans, selon lequel toute expression relationnelle peut être opérande d’un opération relationnelle) :

« Can a table expression be used anywhere a table name is used? »

La réponse est évidemment positive, puisque :

« A table name is merely the trivial case of table expression! »

La norme SQL a suivi Darwen (qui y fut un des représentants pour le compte du Royaume-Uni) et depuis la sortie de la version SQL-1992 de cette norme, on peut effectuer des jointures auxquelles participent non plus seulement des noms de tables, mais des expressions. Les SGBD s’y sont mis, petit à petit.


Pour reprendre la requête :

SELECT prixmatos.idmat, prixmatos.datemaj, prixmatos.prix 
FROM   prixmatos INNER JOIN T ON prixmatos.idmat = T.idmat AND prixmatos.datemaj = T.datemax ;

On peut donc y remplacer la table T par l’expression qu’elle représente :

SELECT prixmatos.idmat, prixmatos.datemaj, prixmatos.prix
FROM   prixmatos 
INNER JOIN 
(
 SELECT matos.idmat, max(prixmatos.datemaj) AS datemax 
 FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
 GROUP BY matos.idmat
)  AS T 
ON prixmatos.idmat = T.idmat AND prixmatos.datemaj = T.datemax ;

Est-ce plus clair ? Cela reste-t-il brumeux ?

[fsmrel]

Bonsoir à nouveau py86acces,

Concernant la requête, je viens de m apercevoir que les produits n'ayant pas de prix n’apparaissent pas.
est ce qu’il y a un moyen de mettre 0.00€

Bien sûr qu’il y a un moyen...


Dans mon message précédent, j’ai écrit :

« Reprenons la requête :

 SELECT matos.idmat, max(prixmatos.datemaj) AS datemax 
 FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
 GROUP BY matos.idmat ;

Le résultat est une table, appelons-la T, comportant deux colonnes, idmat et datemax. Elle contient une ligne par idmat ayant répondu présent lors de la jointure :

 
T    idmat    datemax
     -----    ---------- 
         1    01/07/2015 
         2    01/07/2015 
         4    01/07/2015 

Le produit pour lequel idmat = 3 ne figure pas parce qu’absent de la table prixmatos. »

Pour que ce produit figure dans le résultat, on va utiliser un LEFT JOIN au lieu de l’INNER JOIN :

 SELECT matos.idmat, nz(max(prixmatos.datemaj),'31/12/9999') AS datemax 
 FROM   matos LEFT JOIN prixmatos ON matos.idmat = prixmatos.idmat 
 GROUP BY matos.idmat ;

Où la fonction nz a pour objet de remplacer Null par quelque chose qui ressemble à une date, '31/12/9999' par exemple.


Reprenons la requête du message précédent, en remplaçant déjà la partie interne par ce qu’on vient de faire :

SELECT prixmatos.idmat, prixmatos.datemaj, prixmatos.prix
FROM   prixmatos 
INNER JOIN 
(
 SELECT matos.idmat, nz(max(prixmatos.datemaj),'31/12/9999') AS datemax 
 FROM   matos LEFT JOIN prixmatos ON matos.idmat = prixmatos.idmat 
 GROUP BY matos.idmat
)  AS T 
ON prixmatos.idmat = T.idmat AND prixmatos.datemaj = T.datemax ;

Mais à nouveau, la jointure de T avec la table prixmatos impose un outer join, en l’occurrence un RIGHT JOIN :

SELECT t.idmat, t.datemax, nz(prixmatos.prix,0) AS prix
FROM   prixmatos 
RIGHT JOIN 
(
 SELECT matos.idmat, nz(max(prixmatos.datemaj),'31/12/9999') AS datemax 
 FROM   matos LEFT JOIN prixmatos ON matos.idmat = prixmatos.idmat 
 GROUP BY matos.idmat
)  AS T 
ON prixmatos.idmat = T.idmat AND prixmatos.datemaj = Cdate(T.datemax) ;

Au résultat, le produit 3 est là :

Idmat    datemax     prix
----     -------     ----
    1    01/07/2015    15
    2    01/07/2015    20
    3    31/12/9999     0
    4    01/07/2015    40

J’espère ne pas m’être planté dans les copier/coller...

[fsmrel]

Bonjour py86acces,

Concernant la requête, je viens de m apercevoir que les produits n'ayant pas de prix n’apparaissent pas.
est ce qu’il y a un moyen de mettre 0.00€

Dans mon message précédent, je vous ai proposé une solution faisant intervenir l’outer join (LEFT JOIN et RIGHT JOIN). Mais ces opérateurs ne sont pas indispensables et d’autre part l’expression comportant le LEFT JOIN produit plus de lignes (l’obésité guette) que celle qui contient l’INNER JOIN et fait intervenir un des opérateurs (NZ) qu’il a fallu inventer pour contenir l’ennemi des optimiseurs et de la logique aristotélicienne, à savoir le bonhomme Null...

LEFT JOIN :

 SELECT matos.idmat, NZ(MAX(prixmatos.datemaj),'31/12/9999') AS datemax 
 FROM   matos LEFT JOIN prixmatos ON matos.idmat = prixmatos.idmat 
 GROUP BY matos.idmat

INNER JOIN

 SELECT matos.idmat, MAX(prixmatos.datemaj) AS datemax
 FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat
 GROUP BY matos.idmat

Le produit pour lequel idmat = 3 n’appartient pas à la table prixmatos, en conséquence de quoi l’INNER JOIN est impuissant à le faire apparaître dans le résultat final. Toutefois, on sait produire très simplement une table des produits sans prix :

SELECT idmat, '/', 0
FROM   matos
WHERE  NOT EXISTS
       (SELECT ''
        FROM   prixmatos
        WHERE  matos.idmat = prixmatos.idmat)
;

Pour produire un résultat contenant tous les produits, il suffit de procéder à l’UNION des produits présents dans prixmatos et des produits qui en sont absents :

SELECT t.idmat, t.datemax, prixmatos.prix 
FROM 
(   
 SELECT matos.idmat, MAX(prixmatos.datemaj) as datemax
 FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat
 GROUP BY matos.idmat
) as T
INNER JOIN prixmatos
ON prixmatos.idmat = T.idmat and  prixmatos.datemaj = T.datemax

UNION ALL

SELECT idmat, '/', 0
FROM   matos
WHERE  NOT EXISTS
       (SELECT ''
        FROM   prixmatos
        WHERE  matos.idmat = prixmatos.idmat)
;

Au résultat :

Idmat    datemax     prix
----     -------     ----
    1    01/07/2015    15
    2    01/07/2015    20
    3    /              0
    4    01/07/2015    40

C’est la solution que j’utilise sur le terrain, car si intellectuellement parlant elle est moins excitante, concrètement elle est plus performante, elle n’a pas besoin de faire appel à l'outer join avec tous ses parfums, elle n'a pas besoin de faire appel non plus aux fonctions nécessaires pour déguiser le bonhomme Null consubstantiel à l'outer join, etc.

[py86acces]

Bonsoir
Que dire de tous cela
Tous simplement merci

Je vais prendre un peu de temps pour bien comprendre et surtout a tête reposé
je reviendrai demain pour la conclusion enfin .....

Bonne soirée

ET ...........

[py86acces]

Bonjour
OK cela marche un grand merci
pour la compréhension du code entier il va me falloir un peu de temps
mais bon j travail je suis qu'au début des requêtes

juste une dernière question
j'ai voulu concaténé deux colonne de ma table matos
le nom et la désignation
le seul moyen que j'ai trouver est de faire une requête avec mes deux champs
[nom] & "-" [designation] et de rajout votre requeté

CELA MARCHE
MAIS PEUT ON LE METTRE DIRECTEMENT DANS LA PREMIERE REQUETE AVEC concat
MAIS J'AI DES MESSAGE ERREUR DE SYNTAXE
LE BUT ETANT DE MULTIPLIER LES REQUÊTES
MERCI

[fsmrel]

Bonjour py86acces,


L’attribut idmat est clé de la table matos, donc pour une valeur de idmat il y a au plus une valeur de nomat et une valeur de desmat, en vertu de quoi chaque valeur de triplet {matos.idmat, matos.nomat, matos.desmat} est unique.

Dans la requête :

SELECT matos.idmat, max(prixmatos.datemaj) AS datemax 
FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
GROUP BY matos.idmat

Vous pouvez donc compléter ainsi la partie regroupement « GROUP BY matos.idmat » :

SELECT matos.idmat, max(prixmatos.datemaj) AS datemax 
FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
GROUP BY matos.idmat, matos.nomat, matos.desmat

Ce qui vous donne le droit de coder :

SELECT matos.idmat, max(prixmatos.datemaj) AS datemax, nomat & ' - ' & desmat as libelle
FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
GROUP BY matos.idmat, matos.nomat, matos.desmat

=>

Idmat    datemax     prix    libelle
----     -------     ----    -------
    1    01/07/2015    15    x - xxx
    2    01/07/2015    20    a - aaa
    4    01/07/2015    40    s - sss

D’où la requête finale :

SELECT T.idmat, datemax, prixmatos.prix, libelle
FROM 
(
    SELECT matos.idmat, max(prixmatos.datemaj) AS datemax, nomat & ' - ' & desmat as libelle 
    FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
    GROUP BY matos.idmat, matos.nomat, matos.desmat)  AS T 

INNER JOIN prixmatos ON (prixmatos.idmat = T.idmat) AND (prixmatos.datemaj = T.datemax

)

UNION ALL 

SELECT idmat, '/', 0, '/ - ////////'
FROM   matos
WHERE  NOT EXISTS
       (SELECT ''
        FROM   prixmatos
        WHERE  matos.idmat = prixmatos.idmat);

Au résultat :

Idmat    datemax     prix    libelle
----     -------     ----    -------
    1    01/07/2015    15    x - xxx
    2    01/07/2015    20    a - aaa
    3    /              0    / - ////////
    4    01/07/2015    40    s - sss

Est-ce bien ce que vous attendiez ?

[byaccess2]

Bonjour,
je me permets d'intervenir dans ce post car la demande rejoint en tous points celle que j'ai formulée ailleurs dans ce forum:
http://www.developpez.net/forums/d15...e/#post8153367
voyant que votre solution combine les critères de py86acces, pourriez-vous jeter un oeil sur mon soucis
en vous remerciant d'avance

[fsmrel]

@byacces2

Bon, je vais essayer de trouver un moment pour prendre connaissance de votre problème

[py86acces]

Bonsoir et merci
je pense que je dois faire des excuses
Oui c'est bien cela mais a trop regarder la requete pour la comprendre ( ce que je commence juste a comprendre une partie )
EN REALITE
je veux concatener EFFECTIVEMENT
le champ DES ( designation) de ma table MATOS
ET
le champ STAT ( Statut ) de ma table MATOS

MAIS J'AVAIS OUBLIER DE DIRE ET C LA QUE JE FAIT MES EXCUSES QUE

le champ STAT de ma table MATOS vient d'une table STATUT

IDstat nom
1 Statut1
2 Statut2
3 Statut3

Donc quand je modifie la requete avec les modif cela marche très bien
IL ME DONNE CELA
1- XXX

MAIS JE VOUDRAIS
Statut1-XXX

Désolé pour l'energie depensé et je comprendrai si la coupe est pleine
bonsoir

[fsmrel]

Bonsoir py86acces,


Ne soyez pas désolé ! ne vous faites de mouron, et puis votre requête devient intéressante

Il y a simplement une jointure avec la table Statut à rajouter :

SELECT T.idmat, datemax, prixmatos.prix, desmat & ' - ' & StatutNom AS designation_statut
FROM 
   (
    SELECT matos.idmat, max(prixmatos.datemaj) AS datemax, desmat, statut.nom AS StatutNom 
    FROM   prixmatos INNER JOIN (matos INNER JOIN statut ON matos.idstat = statut.idstat) ON matos.idmat = prixmatos.idmat 
    GROUP BY matos.idmat, matos.desmat, statut.nom
   )  AS T 

INNER JOIN prixmatos ON prixmatos.idmat = T.idmat AND prixmatos.datemaj = T.datemax

UNION ALL 

SELECT idmat, '/', 0, desmat & ' - ' & statut.nom
FROM   matos INNER JOIN statut ON matos.idstat = statut.idstat
WHERE  NOT EXISTS
       (SELECT ''
        FROM   prixmatos
        WHERE  matos.idmat = prixmatos.idmat);

=>

Idmat    datemax     prix    designation_statut
-----    ----------  ----    ------------------
    1    01/07/2015    15    xxxxx - Statut 1
    2    01/07/2015    20    aaaaa - Statut 3
    4    01/07/2015    40    sssss - Statut 1
    3    /              0    fffff - Statut 2

Le seul problème qui se pose est que cet abruti d’ACCESS a oublié que la jointure naturelle est associative et commutative, aussi nous force-t-il à emboîter les jointures comme des poupées russes, ce qui n’est pas fait pour nous simplifier la vie...

En tout cas, n’oubliez pas de voter pour les réponses qui ont pu vous aider ! Les gros pouces verts sont là pour ça !

[fsmrel]

reBonsoir py86acces,


Si ça vous paraît devenir un peu compliqué, on peut aussi procéder ainsi :


1) créons une requête (en réalité une vue) pour récupérer le statut de chaque matos, appelons-la matos_join_statut :

SELECT matos.idmat, desmat & ' - ' & statut.nom AS designation_statut
FROM   matos INNER JOIN statut ON matos.idstat = statut.idstat ;

2) créons une requête (encore une vue) pour récupérer le dernier prix (et sa date) de chaque matos, appelons-la matos_join_prixmatos :

SELECT prixmatos.idmat, prixmatos.datemaj, prixmatos.prix
FROM 
(
    SELECT matos.idmat, max(prixmatos.datemaj) AS datemax 
    FROM   matos INNER JOIN prixmatos ON matos.idmat = prixmatos.idmat 
    GROUP BY matos.idmat
)  AS T 

INNER JOIN prixmatos ON T.datemax = prixmatos.datemaj AND T.idmat = prixmatos.idmat ;

3) On procède à la jointure des deux vues, tout en complétant avec l’UNION ALL pour récupérer les matos sans prix :

SELECT x.idmat, prix, datemaj, designation_statut
FROM   matos_join_prixmatos AS x INNER JOIN matos_join_statut AS y ON x.idmat = y.idmat

UNION ALL 

SELECT idmat, '/', 0, desmat & ' - ' & statut.nom
FROM   matos INNER JOIN statut ON matos.idstat = statut.idstat
WHERE  NOT EXISTS
       (SELECT ''
        FROM   prixmatos
        WHERE  matos.idmat = prixmatos.idmat);

=>

Idmat    datemax     prix    designation_statut
-----    ----------  ----    ------------------
    1    01/07/2015    15    xxxxx - Statut 1
    2    01/07/2015    20    aaaaa - Statut 3
    4    01/07/2015    40    sssss - Statut 1
    3    /              0    fffff - Statut 2

A vous de voir...