Discussion :

[tomtalf]

Hello

je m'acharne sur un problème que j'aimerais résoudre dans access sans trouver de solutions, peut-être pourrez-vous m'aider.

J'ai deux tables, une avec des positions d'inventaire, l'autre avec un historique de réception par fournisseur:

Table 1:

Produit	Quantité
Produit A	50
Produit B	100

Table 2:

Produit	Fournisseur	Date de réception	Quantité
Produit A	Fournisseur 1	31/03/2014	60
Produit B	Fournisseur 1	31/03/2014	25
Produit B	Fournisseur 2	31/05/2014	60
Produit B	Fournisseur 3	31/07/2014	30

Et je voudrais faire une requete qui regarde mon stock aujourd'hui et me le décompose par fournisseur selon les dernières livraisons (sachant que les produits reçus en premier sortent en premier). Ici le résultat serait donc:

Produit	Fournisseur	Quantité
Produit A	Fournisseur 1	50
Produit B	Fournisseur 1	10
Produit B	Fournisseur 2	60
Produit B	Fournisseur 3	30

Auriez-vous une piste pour commencer?

merci!

tom

[ClaudeLELOUP]

Bonjour Tom,

À l’aide d’une requête, ça me semble difficile… Mais, il y a sur ce forum des virtuoses du SQL.

Par contre en VBA, si tu ajoutes dans Table2 une colonne « EnStock », on peut procéder en 2 temps :

1° pour chacun des enregistrements de Table 1, lire Table2 dans l’ordre chronologique inverse et inscrire dans EnStock la portion qui s’y trouve encore ;

2° une requête sur Table2 qui fournit ton résultat en exploitant la colonne « EnStock ».
Si cette solution te convient , fais-moi signe si tu veux un coup de main.

[tomtalf]

Bonjour Claude

merci de ta réponse

effectivement ça pourrait être une solution, même si j'espérais pouvoir éviter le vba (je maitrise bien dans excel, mais suis plutôt nouveau sur access).

ça pourrait tourner sur des tables de 10000+ lignes?

thomas

[ClaudeLELOUP]

ça pourrait tourner sur des tables de 10000+ lignes?

A priori, pourquoi pas ?

En tout cas, si ça prend trop de temps, il sera encore tôt assez pour chercher un moyen d'optimiser.

[vodiem]

Salut ClaudeLELOUP et bienvenu tomtalf,

> conserver les produits concernés de l'historique:

Code SQL :

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

SELECT historique.* FROM historique LEFT JOIN inventaire ON historique.Produit = inventaire.Produit

> cumuler le stock des produits à partir du plus récents jusqu'avant la date de chaque entrée (inversion chronologique du cumul du stock)

Code SQL :

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

1
2
3
4
SELECT
   historique.*,
   Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#") AS Cumul
FROM historique LEFT JOIN inventaire ON historique.Produit = inventaire.Produit

> quantité inventaire - cumul du stock inversé = reste de stock à retrouver

Code SQL :

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

1
2
3
4
SELECT
   historique.*,
   inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#") AS Reste
FROM historique LEFT JOIN inventaire ON historique.Produit = inventaire.Produit

> mais vu que le premier cumul est vide (quantité null puisqu'il n'y a pas de produit dont la date est supérieur à la derniere entrée...)
on corrige ce défaut:

Code SQL :

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

1
2
3
4
SELECT
   historique.*,
   nz(inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#"), inventaire.Quantité) AS Reste
FROM historique LEFT JOIN inventaire ON historique.Produit = inventaire.Produit

> à ce stade on filtre l'historique pour ne plus avoir les entrées qui ne sont plus concernées: (produit dans l'historique à retrouver implique que le reste à trouver soit positif)

Code SQL :

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

1
2
3
4
5
6
SELECT
   historique.*,
   nz(inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#"), inventaire.Quantité) AS Reste
FROM historique LEFT JOIN inventaire ON historique.Produit = inventaire.Produit
WHERE
      nz(inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#"), inventaire.Quantité)>=0

> reste à afficher la quantité attribuée/disponible :
si Quantité dans l'historique<=Reste de stock à retrouver alors Quantité attribuée/disponible=Quantité de l'historique sinon Quantité attribuée/disponible=Reste de stock à retrouver

Code SQL :

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

1
2
3
4
5
6
SELECT
   historique.*,
   iif(nz(inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#"), inventaire.Quantité)>=historique.Quantité,historique.Quantité,nz(inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#"), inventaire.Quantité)) As Disponible
FROM historique LEFT JOIN inventaire ON historique.Produit = inventaire.Produit
WHERE
      nz(inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#"), inventaire.Quantité)>=0

> présentation du résulat final:

Code SQL :

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

1
2
3
4
5
6
7
8
9
SELECT
   historique.produit,
   historique.fournisseur,
   iif(nz(inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#"), inventaire.Quantité)>=historique.Quantité,historique.Quantité,nz(inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#"), inventaire.Quantité)) As [Quantité disponible]
FROM historique LEFT JOIN inventaire ON historique.Produit = inventaire.Produit
WHERE
      nz(inventaire.Quantité-Dsum("Quantité","historique","[Produit]='" & inventaire.Produit & "' AND [Date de réception]>#" & historique.[Date de réception] & "#"), inventaire.Quantité)>=0
ORDER BY
   historique.produit, historique.fournisseur

[ClaudeLELOUP]

Bonjour Tom, Vodiem et les autres s'il en est,

Vodiem et moi avons échangé quelques messages au sujet de sa proposition.

J’ai rédigé un texte qui décortique, pas à pas, la traduction en requêtes de son idée « Cumul à rebours » et « Reste à trouver ».

L’article se trouve ici : http://claudeleloup.developpez.com/t...plic-ploc/#LXI
et la BdD de test ici : http://claudeleloup.developpez.com/t...tockVentil.mdb

La conclusion
une première requête enregistrée (rEnStockCalculs) pour préparer les calculs :

Code :

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

1
2
3
SELECT historique.Produit, historique.Fournisseur, historique.[Date de réception], historique.Quantité, DSum("Quantité","historique","[Produit]='" & [historique].[Produit] & "' AND [Date de réception]>= #" & Format([historique].[Date de réception],"mm/dd/yyyy") & "#") AS [Cumul inversé], [Inventaire].[Quantité]-[Cumul inversé] AS [Reste à trouver], IIf([Reste à Trouver]>=0,[Historique].[Quantité],[Inventaire].[Quantité]-nz(DSum("Quantité","historique","[Produit]='" & [historique].[Produit] & "' AND [Date de réception]> #" & Format([historique].[Date de réception],"mm/dd/yyyy") & "#"),0)) AS Complément
FROM historique INNER JOIN inventaire ON historique.Produit = inventaire.Produit
ORDER BY historique.Produit, historique.[Date de réception] DESC;

et une seconde requête (rEnStockVentil) pour afficher le résultat :

Code :

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

1
2
3
SELECT rEnStockCalculs.Produit, rEnStockCalculs.Fournisseur, rEnStockCalculs.Complément AS [Encore en stock]
FROM rEnStockCalculs
WHERE (((rEnStockCalculs.Complément)>0))

;

[tomtalf]

Claude, Vodiem

merci infiniment pour vos réponses et le temps que vous avez du y passer, ça m'a été d'une très grande aide.

Peut-être une question complémentaire si vous n'en avez pas encore marre: comment modifier la formule du champ Cumul Inversé dans la requete enStockCalcul pour qu'elle puisse gérer le cas où j'ai le même jour deux réceptions du même fournisseur. dans l'état actuel de la formule, j'ai l'impression que le stock n'est décrémenté qu'une fois , de l'une des deux réceptions, alors qu'il devrait l'être de la somme des deux (je peux bien sur faire une nouvelle table où ces réceptions seraient déjà sommées, il y a peut être une autre solution)

merci en tout cas

tom

[ClaudeLELOUP]

Bonjour tomtalf,

(je peux bien sûr faire une nouvelle table où ces réceptions seraient déjà sommées, il y a peut être une autre solution)

Pas nécessairement une table : une requête de regroupement suffit.

Ça me parait ce qu'il y a de plus simple pour rattraper la sauce.

[vodiem]

Petite remarque en passant j'ai mis une jointure left, ClaudeLELOUP un "inner join", il est possible aussi de mettre une jointure right chaque jointure est valable à chacun de faire son choix en pleine connaissance de cause.

Si tu enregistres la date avec son heure, en conservant éventuellement l'affichage le format abrégé, il ne devrait pas y avoir de problème.
C'est la différence entre les fonctions Date() et Now().

[ClaudeLELOUP]

Si tu enregistres la date avec son heure

Encore plus simple !