Évaluation des stocks avec ACCESS (aller plus loin)

Denis Hulo

Objectif : afficher sous forme tabulaire les entrées/sorties et la valorisation du stock.

Niveau requis : avancé.

Commentez cet article : Commentez

- Titre : Évaluation des stocks avec ACCESS (aller plus loin)

- Auteur : Denis Hulo

- Parution : 7 mars 2018

- Public visé :

- Licence : Copyright ® 2017 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. Entrées et sorties du stock

- III. Requêtes sélection basées sur les entrées et sorties du stock

- III-A. rQteSortiesFIFO

- III-B. rQteSortiesLIFO

- III-C. rPUSortiesCMUP

- IV. Requêtes analyse croisée

- IV-A. racFIFO

- IV-A-1. Gros plan sur le champ IndiceSortie

- IV-B. racLIFO

- IV-B-1. Gros plan sur le champ IndiceSortie

- IV-C. racCMUP

- IV-C-1. Gros plan sur le champ IndiceSortie

- V. Formulaire de paramétrage des tableaux

- V-A. Bouton de commande pour l'ouverture des états

- V-B. Boutons de commandes pour se déplacer d'une période à l'autre

- V-B-1. Bouton CmdPremier

- V-B-2. Bouton CmdPrecedent

- V-B-3. Bouton CmdSuivant

- V-B-4. Bouton CmdDernier

- VI. Tableaux de synthèse des données

- VI-A. eFIFO

- VI-A-1. Gros plan sur l'entête de groupe de l'état

- VI-A-2. Gros plan sur le pied de groupe de l'état

- VI-B. eLIFO

- VI-B-1. Gros plan sur l'entête de groupe de l'état

- VI-B-2. Gros plan sur le pied de groupe de l'état

- VI-C. eCMUP

- VI-C-1. Gros plan sur l'entête de groupe de l'état

- VI-C-2. Gros plan sur le pied de groupe de l'état

- VII. Les bases de données à télécharger

- VIII. Remerciements

I - Introduction

On se propose de calculer et d'afficher dans des tableaux les coûts moyens des sorties de stock, en détaillant les quantités et prix unitaires impliqués dans ces calculs, suivant les trois méthodes généralement utilisées en comptabilité :

- - premier entré, premier sorti (FIFO) ;

- - dernier entré, premier sorti (LIFO) ;

- - le coût moyen unitaire pondéré, recalculé après chaque entrée (CMUP).

Méthodes FIFO et LIFO

Pour ce faire, on se basera sur le travail effectué par Claude Leloup dans son article Évaluation des stocks avec ACCESS.

II - Entrées et sorties du stock

Nous aurons besoin tout d'abord de sélectionner les entrées de stock à l'aide d'une requête rEntrees et les sorties grâce à une autre nommée rSorties.

Il nous suffit pour cela de filtrer les données de la table tMouvementsPK sur le champ Mvt :

- mouvements en entrée : Mvt>0 ;

- mouvements en sortie : Mvt<0.

III - Requêtes sélection basées sur les entrées et sorties du stock

III-A - rQteSortiesFIFO

On relie les entrées et sorties sur le champ identifiant le produit en ne conservant pour chaque sortie du produit que les entrées antérieures à elle.

Cette requête va nous permettre d'évaluer, suivant la méthode FIFO, pour chaque entrée associée à une sortie, la quantité que l'on retire pour cette sortie.

Voici le détail des données et des résultats par mouvement en entrée, accompagné de la formule associée au PU en sortie :

Requête rQteSortiesFIFO

Voici la requête en mode création :

Requête rQteSortiesFIFO

Filtres sur les données

- On filtre sur le produit choisi dans le formulaire fParamPeriode.

- On filtre sur la date de sortie en fonction du choix effectué dans ce formulaire.

- On affiche, pour chaque sortie, uniquement les entrées antérieures à elle.

Critères correspondants

- Comme VraiFaux([Forms]![fParamPeriode]![ParamProduit]="[Tous]";"*";[Forms]![fParamPeriode]![ParamProduit]).

- [rSorties].MvtDate between [Forms]![fParamPeriode]![ParamDateDebut] and [Forms]![fParamPeriode]![ParamDateFin].

- rEntrees.tMouvementsPK < [rSorties].[tMouvementsPK].

Champs calculés

- IndiceSortie : renvoie l'indice de sortie en comptant le nombre de sorties depuis la date entrée en paramètre ([Forms]![fParamPeriode]![ParamDate]).

- QteSortie : renvoie la quantité sortie pour l'entrée affichée.

- FormulePUSortie : donne la formule utilisée pour le calcul du prix unitaire en sortie.

Formules des champs calculés

IndiceSortie: "S" & Format(CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and tMouvementsPK<=" & [rSorties].[tMouvementsPK] & " and MvtDate>=#" & Format([Forms]![fParamPeriode]![ParamDateDebut];"mm/jj/aaaa") & "#");"00")
QteSortie: CalculerQteSortieFiFo([rEntrees].[tProduitsFK];[rEntrees].[tMouvementsPK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])
FormulePUSortie: FormulePUSortieFiFo([rSorties].[tProduitsFK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])

Fonctions utilisées dans les champs calculés

- CalculerQteSortieFifo : utilisée pour le calcul de la quantité en sortie pour chaque entrée de produit impliquée dans le calcul.

- FormulePUSortieFifo : utilisée pour générer la formule employée pour le calcul de la quantité en sortie.

fonction CalculerQteSortieFifo

Public Function CalculerQteSortieFifo(Produit As Long, _
NumMVTEntree As Long, NumMVTSortie As Long, Nombre As Double) As Double
Dim oRst As Recordset
Dim dStockAVentiler As Double, dStockRecalcule As Double
Dim sSQL As String
Dim dResteAImputer As Double
'Calcul du stock avant la sortie
dStockAVentiler = -Nz(DSum("Mvt", "tMouvements", "tMouvementsPK<" & NumMVTSortie _
& " AND (tProduitsFK=" & Produit & ") AND (Mvt<0)"), 0)
'Création du recordset des entrées
sSQL = "SELECT Mvt, PUEntree FROM tMouvements " _
& "WHERE tMouvementsPK<=" & NumMVTEntree _
& " AND tProduitsFK=" & Produit _
& " AND Mvt>0;"
Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
'Ventilation du stock par poste d'entrée
'À ce stade, on se trouve sur l'entrée la plus ancienne encore provisionnée
'On va émarger la sortie sur ce poste et les suivants (si nécessaire)
Do While (dStockAVentiler > oRst("Mvt"))
dStockAVentiler = dStockAVentiler - oRst("Mvt")
oRst.MoveNext
If oRst.EOF Then Exit Do
Loop

'Valorisation de la sortie FIFO
dResteAImputer = Nombre

If Not oRst.EOF Then
dStockRecalcule = oRst("Mvt") - dStockAVentiler

Do While Not oRst.EOF

If dResteAImputer > dStockRecalcule Then ' Si le reste à imputer est supérieur à la qté calculée
CalculerQteSortieFifo = dStockRecalcule ' On affecte à la fonction la qté calculée
dResteAImputer = dResteAImputer - dStockRecalcule ' On la déduit du reste à imputer
oRst.MoveNext

If Not oRst.EOF Then dStockRecalcule = oRst("Mvt")

Else ' Sinon on affecte le reste à imputer.
CalculerQteSortieFifo = dResteAImputer
oRst.MoveNext
Exit Do
End If

Loop

End If

'S'il reste un enregistrement, comme tout a déjà été imputé, on le met à 0
If Not oRst.EOF Then
CalculerQteSortieFifo = 0
End If

'Libérer la mémoire
oRst.Close
Set oRst = Nothing
End Function

fonction FormulePUSortieFifo

Public Function FormulePUSortieFifo(Produit As Long, _
NumMVT As Long, Nombre As Double) As String
' On détermine la formule employée pour le calcul du PU en sortie
Dim oRst As Recordset
Dim dStockAVentiler As Double, dStockRecalcule As Double
Dim sSQL As String
Dim dResteAImputer As Double
'Calcul du débit avant la sortie
dStockAVentiler = -Nz(DSum("Mvt", "tMouvements", "tMouvementsPK<" & NumMVT _
& " AND (tProduitsFK=" & Produit & ") AND (Mvt<0)"), 0)
'Création du recordset des entrées
sSQL = "SELECT Mvt, PUEntree FROM tMouvements " _
& "WHERE tMouvementsPK<" & NumMVT _
& " AND tProduitsFK=" & Produit _
& " AND Mvt>0;"
Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
'Ventilation du stock par poste d'entrée

'À ce stade, on se trouve sur l'entrée la plus ancienne encore provisionnée
'On va émarger la sortie sur ce poste et les suivants (si nécessaire)
Do While (dStockAVentiler > oRst("Mvt"))
dStockAVentiler = dStockAVentiler - oRst("Mvt")
oRst.MoveNext
Loop

'Valorisation de la sortie FIFO
dResteAImputer = Nombre
dStockRecalcule = oRst("Mvt") - dStockAVentiler

Do While Not oRst.EOF

If dResteAImputer > dStockRecalcule Then
FormulePUSortieFifo = FormulePUSortieFifo _
& "(" & Round(dStockRecalcule, 2) & "*" & Round(oRst("PUEntree"), 2) & ") + " ' (Qté Sortie * PU Entrée)
dResteAImputer = dResteAImputer - dStockRecalcule
oRst.MoveNext
dStockRecalcule = oRst("Mvt")
Else
FormulePUSortieFifo = FormulePUSortieFifo _
& "(" & Round(dResteAImputer, 2) & "*" & Round(oRst("PUEntree"), 2) & ")" ' (Qté Sortie * PU Entrée)
Exit Do ' On sort de la boucle
End If
Loop
'Formule sortie FIFO
' Si la formule est composée d'au moins 2 termes, on encadre la formule avec des parenthèses
If InStr(FormulePUSortieFifo, "+") <> 0 Then
FormulePUSortieFifo = "(" & FormulePUSortieFifo & ")" & "/" & Nombre
Else ' Sinon
FormulePUSortieFifo = FormulePUSortieFifo & "/" & Nombre
End If
'Libérer la mémoire
oRst.Close
Set oRst = Nothing
End Function

Function FormulePUSortieLifo(Produit As Long, NumMVT As Long, Nombre As Double) As String
' On détermine la formule employée pour le calcul du PU en sortie
Dim oRst As Recordset
Dim dResteAImputer As Double
Dim sSQL As String
Dim dCumulSorties As Double
'Créer le recordset pour lire les mouvements qui précèdent
sSQL = "SELECT Mvt, PUEntree FROM tMouvements" _
& " WHERE tMouvementsPK <" & NumMVT & " And tProduitsFK =" & Produit & ";"
Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
oRst.MoveLast
dResteAImputer = Nombre
Do While Not oRst.EOF
If oRst("Mvt") < 0 Then
'Sortie => il faut cumuler pour l'imputer à l'entrée qui précède
dCumulSorties = dCumulSorties + oRst("Mvt")
Else
'C'est donc une entrée.
'Reste-t-il du disponible ?
If oRst("Mvt") + dCumulSorties > 0 Then
'si oui, de 2 choses l'une :
'- ou bien elle suffit pour alimenter la sortie
If dResteAImputer <= oRst("Mvt") + dCumulSorties Then
FormulePUSortieLifo = FormulePUSortieLifo & _
"(" & Round(dResteAImputer, 2) & "*" & Round(oRst("PUEntree"), 2) & ")" ' (Qté Sortie * PU Entrée)
Exit Do
'- ou bien,il n'y a pas encore assez et il faut continuer
Else
FormulePUSortieLifo = FormulePUSortieLifo & _
"(" & Round((oRst("Mvt") + dCumulSorties), 2) & "*" & Round(oRst("PUEntree"), 2) & ") + " ' (Qté Sortie * PU Entrée)
dResteAImputer = dResteAImputer - (oRst("Mvt") + dCumulSorties)
dCumulSorties = 0
End If
Else
'Sinon, il faut retenir le solde négatif dans dSortiesImputées et passer au suivant
dCumulSorties = oRst("Mvt") + dCumulSorties
End If
End If
oRst.MovePrevious
Loop
'Formule sortie LIFO
If InStr(FormulePUSortieLifo, "+") <> 0 Then ' Si la formule est composée d'au moins 2 termes, on encadre la formule avec des parenthèses
FormulePUSortieLifo = "(" & FormulePUSortieLifo & ")" & "/" & Nombre
Else ' Sinon
FormulePUSortieLifo = FormulePUSortieLifo & "/" & Nombre
End If
'Libérer la mémoire
oRst.Close
Set oRst = Nothing
End Function

III-B - rQteSortiesLIFO

On relie les entrées et sorties sur le champ identifiant le produit, en ne conservant pour chaque sortie de produit que les entrées antérieures à elle.

Cette requête va nous permettre de calculer, suivant la méthode LIFO, pour chaque entrée associée à une sortie, la quantité que l'on retire pour cette sortie.

Voici le détail des données et résultats par mouvement en entrée accompagné de la formule associée au PU en sortie :

Requête rQteSortiesLIFO

Voici la requête en mode création :

Requête rQteSortiesLIFO

Filtres sur les données

- On filtre sur le produit choisi dans le formulaire fParamaPeriode.

- On filtre sur la date de sortie en fonction du choix effectué dans ce formulaire.

- On affiche, pour chaque sortie, uniquement les entrées antérieures à elle.

Critères correspondants

- Comme VraiFaux([Forms]![fParamPeriode]![ParamProduit]="[Tous]";"*";[Forms]![fParamPeriode]![ParamProduit]).

- [rSorties].MvtDate between [Forms]![fParamPeriode]![ParamDateDebut] and [Forms]![fParamPeriode]![ParamDateFin].

- rEntrees.tMouvementsPK < [rSorties].[tMouvementsPK].

Champs calculés

- IndiceSortie : renvoie l'indice de sortie en comptant le nombre de sorties depuis la date entrée en paramètre ([Forms]![fParamPeriode]![ParamDate]).

- QteSortie : renvoie la quantité sortie pour l'entrée affichée.

- FormulePUSortie : donne la formule utilisée pour le calcul du prix unitaire en sortie.

Formules des champs calculés

IndiceSortie: "S" & Format(CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and tMouvementsPK<=" & [rSorties].[tMouvementsPK] & " and MvtDate>=#" & Format([Forms]![fParamPeriode]![ParamDateDebut];"mm/jj/aaaa") & "#");"00")
QteSortie: CalculerQteSortieLiFo([rEntrees].[tProduitsFK];[rEntrees].[tMouvementsPK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])
FormulePUSortie: FormulePUSortieLiFo([rSorties].[tProduitsFK];[rSorties].[tMouvementsPK];-[rSorties].[mvt])

Fonctions utilisées dans les champs calculés

- CalculerQteSortieLifo : utilisée pour le calcul de la quantité en sortie pour chaque entrée de produit impliquée dans le calcul.

- FormulePUSortieLifo : utilisée pour générer la formule employée pour le calcul de la quantité en sortie.

fonction CalculerQteSortieLifo

Public Function CalculerQteSortieLifo(Produit As Long, _
NumMVTEntree As Long, NumMVTSortie As Long, Nombre As Double) As Double
Dim oRst As Recordset
Dim dCumulSorties As Double
Dim sSQL As String
Dim dResteAImputer As Double, NumMVT As Long
'Création du recordset des entrées/sorties
sSQL = "SELECT tMouvementsPK, Mvt, PUEntree FROM tMouvements " _
& "WHERE tMouvementsPK<" & NumMVTSortie _
& " AND tMouvementsPK>=" & NumMVTEntree _
& " AND tProduitsFK=" & Produit

Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

'On se positionne sur le dernier enregistrement en entrée/sortie
oRst.MoveLast

'Valorisation de la sortie LIFO
dResteAImputer = Nombre
dCumulSorties = 0

Do While Not oRst.BOF ' On parcourt la liste des mouvements du dernier au premier

If oRst("Mvt") > 0 Then
'C'est donc une entrée.
'Reste-t-il du disponible ?
If oRst("Mvt") + dCumulSorties > 0 Then
'si oui, de 2 choses l'une :
'- ou bien elle suffit pour alimenter la sortie
If dResteAImputer <= (oRst("Mvt") + dCumulSorties) Then
CalculerQteSortieLifo = (dResteAImputer)
NumMVT = oRst("tMouvementsPK")
Exit Do
'- ou bien,il n'y a pas encore assez et il faut continuer
Else
CalculerQteSortieLifo = oRst("Mvt") + dCumulSorties
dResteAImputer = dResteAImputer - (oRst("Mvt") + dCumulSorties)
dCumulSorties = 0
NumMVT = oRst("tMouvementsPK")
End If
Else
'Sinon, il faut retenir le solde négatif dans dCumulSorties et passer au suivant
dCumulSorties = dCumulSorties + oRst("Mvt")
End If
Else
dCumulSorties = dCumulSorties + oRst("Mvt")
End If

oRst.MovePrevious ' On passe au précédent

Loop

If NumMVT <> NumMVTEntree Then ' Si on n'est pas sorti sur l'entrée à valoriser
CalculerQteSortieLifo = 0
End If

'Libérer la mémoire
oRst.Close
Set oRst = Nothing
End Function

fonction FormulePUSortieLifo

Public Function FormulePUSortieFifo(Produit As Long, _
NumMVT As Long, Nombre As Double) As String
' On détermine la formule employée pour le calcul du PU en sortie
Dim oRst As Recordset
Dim dStockAVentiler As Double, dStockRecalcule As Double
Dim sSQL As String
Dim dResteAImputer As Double
'Calcul du débit avant la sortie
dStockAVentiler = -Nz(DSum("Mvt", "tMouvements", "tMouvementsPK<" & NumMVT _
& " AND (tProduitsFK=" & Produit & ") AND (Mvt<0)"), 0)
'Création du recordset des entrées
sSQL = "SELECT Mvt, PUEntree FROM tMouvements " _
& "WHERE tMouvementsPK<" & NumMVT _
& " AND tProduitsFK=" & Produit _
& " AND Mvt>0;"
Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
'Ventilation du stock par poste d'entrée

'À ce stade, on se trouve sur l'entrée la plus ancienne encore provisionnée
'On va émarger la sortie sur ce poste et les suivants (si nécessaire)
Do While (dStockAVentiler > oRst("Mvt"))
dStockAVentiler = dStockAVentiler - oRst("Mvt")
oRst.MoveNext
Loop

'Valorisation de la sortie FIFO
dResteAImputer = Nombre
dStockRecalcule = oRst("Mvt") - dStockAVentiler

Do While Not oRst.EOF

If dResteAImputer > dStockRecalcule Then
FormulePUSortieFifo = FormulePUSortieFifo _
& "(" & Round(dStockRecalcule, 2) & "*" & Round(oRst("PUEntree"), 2) & ") + " ' (Qté Sortie * PU Entrée)
dResteAImputer = dResteAImputer - dStockRecalcule
oRst.MoveNext
dStockRecalcule = oRst("Mvt")
Else
FormulePUSortieFifo = FormulePUSortieFifo _
& "(" & Round(dResteAImputer, 2) & "*" & Round(oRst("PUEntree"), 2) & ")" ' (Qté Sortie * PU Entrée)
Exit Do ' On sort de la boucle
End If
Loop
'Formule sortie FIFO
' Si la formule est composée d'au moins 2 termes, on encadre la formule avec des parenthèses
If InStr(FormulePUSortieFifo, "+") <> 0 Then
FormulePUSortieFifo = "(" & FormulePUSortieFifo & ")" & "/" & Nombre
Else ' Sinon
FormulePUSortieFifo = FormulePUSortieFifo & "/" & Nombre
End If
'Libérer la mémoire
oRst.Close
Set oRst = Nothing
End Function

Function FormulePUSortieLifo(Produit As Long, NumMVT As Long, Nombre As Double) As String
' On détermine la formule employée pour le calcul du PU en sortie
Dim oRst As Recordset
Dim dResteAImputer As Double
Dim sSQL As String
Dim dCumulSorties As Double
'Créer le recordset pour lire les mouvements qui précèdent
sSQL = "SELECT Mvt, PUEntree FROM tMouvements" _
& " WHERE tMouvementsPK <" & NumMVT & " And tProduitsFK =" & Produit & ";"
Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
oRst.MoveLast
dResteAImputer = Nombre
Do While Not oRst.EOF
If oRst("Mvt") < 0 Then
'Sortie => il faut cumuler pour l'imputer à l'entrée qui précède
dCumulSorties = dCumulSorties + oRst("Mvt")
Else
'C'est donc une entrée.
'Reste-t-il du disponible ?
If oRst("Mvt") + dCumulSorties > 0 Then
'si oui, de 2 choses l'une :
'- ou bien elle suffit pour alimenter la sortie
If dResteAImputer <= oRst("Mvt") + dCumulSorties Then
FormulePUSortieLifo = FormulePUSortieLifo & _
"(" & Round(dResteAImputer, 2) & "*" & Round(oRst("PUEntree"), 2) & ")" ' (Qté Sortie * PU Entrée)
Exit Do
'- ou bien,il n'y a pas encore assez et il faut continuer
Else
FormulePUSortieLifo = FormulePUSortieLifo & _
"(" & Round((oRst("Mvt") + dCumulSorties), 2) & "*" & Round(oRst("PUEntree"), 2) & ") + " ' (Qté Sortie * PU Entrée)
dResteAImputer = dResteAImputer - (oRst("Mvt") + dCumulSorties)
dCumulSorties = 0
End If
Else
'Sinon, il faut retenir le solde négatif dans dSortiesImputées et passer au suivant
dCumulSorties = oRst("Mvt") + dCumulSorties
End If
End If
oRst.MovePrevious
Loop
'Formule sortie LIFO
If InStr(FormulePUSortieLifo, "+") <> 0 Then ' Si la formule est composée d'au moins 2 termes, on encadre la formule avec des parenthèses
FormulePUSortieLifo = "(" & FormulePUSortieLifo & ")" & "/" & Nombre
Else ' Sinon
FormulePUSortieLifo = FormulePUSortieLifo & "/" & Nombre
End If
'Libérer la mémoire
oRst.Close
Set oRst = Nothing
End Function

III-C - rPUSortiesCMUP

Cette requête basée sur les sorties de stock va nous permettre de calculer, suivant la méthode CMUP, le prix unitaire du produit pour chaque sortie.

Voici des exemples de valeurs obtenues pour le prix unitaire de chaque sortie de stock suivant la méthode CMUP :

Requête rPUSortiesCMUP

Voici la requête en mode création :

Requête rPUSortiesCMUP

Filtres sur les données

- On filtre sur le produit choisi dans le formulaire fParamPeriode.

- On filtre sur la date de sortie en fonction du choix effectué dans ce formulaire.

Critères correspondants

- Comme VraiFaux([Forms]![fParamPeriode]![ParamProduit]="[Tous]";"*";[Forms]![fParamPeriode]![ParamProduit]).

- [rSorties].MvtDate between [Forms]![fParamPeriode]![ParamDateDebut] and [Forms]![fParamPeriode]![ParamDateFin].

Champs calculés

- IndiceSortie : renvoie l'indice de sortie en comptant le nombre de sorties depuis la date entrée en paramètre ([Forms]![fParamPeriode]![ParamDate]).

- PUCMUP : renvoie le prix unitaire moyen pondéré (CMUP) fonction des mouvements antérieurs.

Formules des champs calculés

IndiceSortie: "S" & Format(CpteDom("*";"rSorties";"tProduitsFK=" & [rSorties].[tProduitsFK] & " and tMouvementsPK<=" & [rSorties].[tMouvementsPK] & " and MvtDate>=#" & Format([Forms]![fParamPeriode]![ParamDateDebut];"mm/jj/aaaa") & "#");"00")
PUCMUP: CalculerPUSortieCMUP([tProduitsFK];[tMouvementsPK])

Fonction utilisée dans le champ calculé

- CalculerPUSortieCMUP : utilisée pour calculer le prix unitaire en sortie suivant la méthode CMUP.

fonction CalculerPUSortieCMUP

Public Function CalculerPUSortieCMUP(Produit As Long, _
NumMVT As Long) As Double
' Évaluation du prix unitaire en sortie suivant la méthode CMUP
Dim oRst As Recordset
Dim dStockValeur As Double, dStockQte As Double, dStockPU As Double
Dim sSQL As String

'Création du recordset des entrées
sSQL = "SELECT Mvt, PUEntree FROM tMouvements " _
& "WHERE tMouvementsPK<" & NumMVT _
& " AND tProduitsFK=" & Produit

Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

'Valorisation de la sortie CMUP

Do While Not oRst.EOF

If oRst("Mvt") > 0 Then ' Si le mouvement est une entrée
dStockValeur = dStockValeur + oRst("Mvt") * oRst("PUEntree") ' Valeur du stock
dStockQte = dStockQte + oRst("Mvt") ' Quantité en stock
dStockPU = dStockValeur / dStockQte ' Prix Unitaire CMUP
Else ' Si c'est une sortie
dStockValeur = dStockValeur + oRst("Mvt") * dStockPU ' Valeur du stock
dStockQte = dStockQte + oRst("Mvt") ' Quantité en stock
End If

oRst.MoveNext

Loop

'Prix unitaire sortie CMUP
CalculerPUSortieCMUP = dStockPU
'Libérer la mémoire
oRst.Close
Set oRst = Nothing
End Function

IV - Requêtes analyse croisée

IV-A - racFIFO

On croise les données issues de la requête rQteSortiesFIFO avec les entrées en entêtes de ligne, les indices des sorties en colonnes, et les quantités sorties à l'intersection.

Voici le détail de la requête en mode création :

Requête racFIFO

formule du champ valeur

TotSortieFIFO: Somme(VraiFaux([QteSortie]=0;Null;[QteSortie]))

IV-A-1 - Gros plan sur le champ IndiceSortie

Ce champ permet de générer des entêtes de colonne fixes en figeant les noms des sorties avec les termes successifs « S01 »,« S02 »,« S03 », …,« S10 » appliqués à la propriété « entêtes des colonnes ».

entêtes des colonnes

IV-B - racLIFO

On croise les données issues de la requête rQteSortiesLIFO avec les entrées en entêtes de ligne, les indices des sorties en colonnes, et les quantités sorties à l'intersection.

Voici le détail de la requête en mode création :

Requête racLIFO

formule du champ valeur

TotSortieLIFO: Somme(VraiFaux([QteSortie]=0;Null;[QteSortie]))

IV-B-1 - Gros plan sur le champ IndiceSortie

Ce champ permet de générer des entêtes de colonne fixes en figeant les noms des sorties avec les termes successifs « S01 »,« S02 »,« S03 », …,« S10 » appliqués à la propriété « entêtes des colonnes ».

entêtes des colonnes

IV-C - racCMUP

On croise les données issues de la requête rPUSortiesCMUP avec les sorties en entêtes de lignes, les indices des sorties en colonne, et les quantités sorties à l'intersection.

Voici le détail de la requête en mode création :

Requête racCMUP

formule du champ valeur

TotSortieCMUP: Somme(VraiFaux([MvtSortie]=0;Null;[MvtSortie]))

IV-C-1 - Gros plan sur le champ IndiceSortie

Ce champ permet de générer des entêtes de colonne fixes en figeant les noms des sorties avec les termes successifs « S01 »,« S02 »,« S03 », …,« S10 » appliqués à la propriété « entêtes des colonnes ».

entêtes des colonnes

V - Formulaire de paramétrage des tableaux

Il va servir à définir la période de filtrage des sorties et le ou les produits à afficher sur les tableaux de synthèse.

Formulaire fParamPeriode

V-A - Bouton de commande pour l'ouverture des états

Permet d'afficher la synthèse des sorties suivant la méthode FIFO, LIFO ou CMUP et la période choisies sur le formulaire.

Bouton de commande CmdValider

Code sur l'événement clic du bouton CmdValider

Private Sub CmdValider_Click()
' On ouvre le tableau de synthèse de la valorisation des stocks suivant la méthode choisie dans la liste

DoCmd.Minimize ' On minimise le formulaire de paramétrage

Select Case Me.ParamMethode.Value

Case "FIFO" ' Si la méthode FIFO a été choisie
DoCmd.Close acReport, "eFIFO" ' On ferme l'état si déjà ouvert
DoCmd.OpenReport "eFIFO", acViewPreview ' On ouvre l'état en mode aperçu avant impression

Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview

Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview

End Select

End Sub

V-B - Boutons de commandes pour se déplacer d'une période à l'autre

Permet de se déplacer dans le temps et d'afficher l'état correspondant à la période choisie.

Boutons de déplacement

La base jointe au format accdb dispose en plus de boutons de commande sur le ruban lié aux états pour se déplacer dans le temps.

V-B-1 - Bouton CmdPremier

Il permet d'afficher les premières sorties sur les tableaux de synthèse.

Procédure événementielle sur clic

Private Sub CmdPremier_Click()
' On passe aux premières sorties
CmdPrem ' Exécution de la procédure CmdPrem
End Sub

Procédure CmdPrem

Public Sub CmdPrem()
' Commande l'affichage des premières sorties sur les tableaux de synthèse
Dim datDebut As Date, datFin As Date

datDebut = DMin("MvtDate", "rSorties") ' Plus ancienne date de sortie

If (Forms!fParamPeriode.ParamProduit = "[Tous]") Then ' Si tous les produits
datFin = EvaluerDateFin(0, datDebut) ' Évaluation de la date de fin de la période
Else ' Sinon
datFin = EvaluerDateFin(Forms!fParamPeriode.ParamProduit, datDebut) ' Évaluation de la date de fin de la période
End If

' Affectation des dates aux zones de texte
Forms!fParamPeriode.ParamDateDebut = datDebut
Forms!fParamPeriode.ParamDateFin = datFin

DoCmd.Minimize ' On minimise le formulaire de paramétrage

'Ouverture de l'état correspondant à la méthode choisie
Select Case Forms!fParamPeriode.ParamMethode.Value

Case "FIFO"
DoCmd.Close acReport, "eFIFO"
DoCmd.OpenReport "eFIFO", acViewPreview

Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview

Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview

End Select

End Sub

V-B-2 - Bouton CmdPrecedent

Il permet d'afficher les sorties précédentes sur les tableaux de synthèse.

Procédure événementielle sur clic

Private Sub CmdPrecedent_Click()
' On passe aux sorties précédentes
CmdPrec ' Exécution de la procédure CmdPrec
End Sub

Procédure CmdPrec

Public Sub CmdPrec()
' Commande l'affichage des sorties précédentes sur les tableaux de synthèse
Dim datDebut As Date, datFin As Date

datFin = CDate(Forms!fParamPeriode.ParamDateDebut) - 1 ' Date de fin de la période

If (Forms!fParamPeriode.ParamProduit = "[Tous]") Then ' Si tous les produits
datDebut = EvaluerDateDebut(0, datFin) ' Évaluation de la date de début de la période
Else ' Sinon
datDebut = EvaluerDateDebut(Forms!fParamPeriode.ParamProduit, datFin) ' Évaluation de la date de début de la période
End If

If IsNull(DLookup("MvtDate", "rSorties", "MvtDate between #" & Format(datDebut, "mm/dd/yyyy") _
& "# and #" & Format(datFin, "mm/dd/yyyy") & "#")) Then
' Si pas de sortie pour cette période
MsgBox ("Pas de sorties disponibles !")
Exit Sub
End If

' Affectation des dates aux zones de texte
Forms!fParamPeriode.ParamDateDebut = datDebut
Forms!fParamPeriode.ParamDateFin = datFin

DoCmd.Minimize ' On minimise le formulaire de paramétrage

'Ouverture de l'état correspondant à la méthode choisie
Select Case Forms!fParamPeriode.ParamMethode.Value

Case "FIFO"
DoCmd.Close acReport, "eFIFO"
DoCmd.OpenReport "eFIFO", acViewPreview

Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview

Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview

End Select

End Sub

V-B-3 - Bouton CmdSuivant

Il permet d'afficher les sorties suivantes sur les tableaux de synthèse.

Procédure événementielle sur clic

Private Sub CmdSuivant_Click()
' On passe aux sorties suivantes
CmdSuiv ' Exécution de la procédure CmdSuiv
End Sub

Procédure CmdSuiv

Public Sub CmdSuiv()
' Commande l'affichage des sorties suivantes sur les tableaux de synthèse
Dim datDebut As Date, datFin As Date

datDebut = CDate(Forms!fParamPeriode.ParamDateFin) + 1 ' Date de début de la période

If (Forms!fParamPeriode.ParamProduit = "[Tous]") Then ' Si tous les produits
datFin = EvaluerDateFin(0, datDebut) ' On évalue la date de fin de la période
Else ' Sinon
datFin = EvaluerDateFin(Forms!fParamPeriode.ParamProduit, datDebut) ' On évalue la date de fin de la période
End If

If IsNull(DLookup("MvtDate", "rSorties", "MvtDate between #" & Format(datDebut, "mm/dd/yyyy") _
& "# and #" & Format(datFin, "mm/dd/yyyy") & "#")) Then
' Si pas de sortie pour cette période
MsgBox ("Pas de sorties disponibles !")
Exit Sub
End If

' Affectation des dates aux zones de texte
Forms!fParamPeriode.ParamDateDebut = datDebut
Forms!fParamPeriode.ParamDateFin = datFin

DoCmd.Minimize ' On minimise le formulaire de paramétrage

'Ouverture de l'état correspondant à la méthode choisie
Select Case Forms!fParamPeriode.ParamMethode.Value

Case "FIFO"
DoCmd.Close acReport, "eFIFO"
DoCmd.OpenReport "eFIFO", acViewPreview

Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview

Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview

End Select

End Sub

V-B-4 - Bouton CmdDernier

Il permet d'afficher les dernières sorties sur les tableaux de synthèse.

Procédure événementielle sur clic

Private Sub CmdDernier_Click()
' On passe aux dernières sorties
CmdDern ' Exécution de la procédure CmdDern
End Sub

Procédure événementielle sur clic

Public Sub CmdDern()
' Commande l'affichage des dernières sorties sur les tableaux de synthèse
Dim datDebut As Date, datFin As Date

datFin = DMax("MvtDate", "rSorties") ' Date de la dernière sortie

If (Forms!fParamPeriode.ParamProduit = "[Tous]") Then ' Si tous les produits
datDebut = EvaluerDateDebut(0, datFin) ' Évaluation de la date de début de la période
Else ' Sinon
datDebut = EvaluerDateDebut(Forms!fParamPeriode.ParamProduit, datFin) ' Évaluation de la date de début de la période
End If

' Affectation des dates aux zones de texte
Forms!fParamPeriode.ParamDateDebut = datDebut
Forms!fParamPeriode.ParamDateFin = datFin

DoCmd.Minimize ' On minimise le formulaire de paramétrage

'Ouverture de l'état correspondant à la méthode choisie
Select Case Forms!fParamPeriode.ParamMethode.Value

Case "FIFO"
DoCmd.Close acReport, "eFIFO"
DoCmd.OpenReport "eFIFO", acViewPreview

Case "LIFO"
DoCmd.Close acReport, "eLIFO"
DoCmd.OpenReport "eLIFO", acViewPreview

Case "CMUP"
DoCmd.Close acReport, "eCMUP"
DoCmd.OpenReport "eCMUP", acViewPreview

End Select

End Sub

VI - Tableaux de synthèse des données

Nous présentons les données dans des états, sous forme tabulaire avec un regroupement par produit :

- en entêtes de ligne les entrées du stock ;

- en colonnes les indices des sorties du stock ;

- à l'intersection des deux les quantités sorties ;

- dans le pied du groupe les valeurs sorties du stock par produit (Qté, PU, Total en sortie).

VI-A - eFIFO

Tableau de synthèse de la valorisation des stocks (méthode FIFO) :

Valorisation du stock : méthode FIFO

Le tri se fait par produit et mouvement en entrée du stock.

VI-A-1 - Gros plan sur l'entête de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut afficher dans son entête les identifiants des mouvements en sortie (tMouvementsPK) pour chaque produit :

entête de groupe de l'état

On met à jour les entêtes de colonne du groupe au moyen d'une procédure événementielle exécutée sur Impression de l'entête de groupe.

entêtes de colonne

- Étiquettes pour les numéros des mouvements en sortie : « EnteteS01 », « EnteteS02 », …,« EnteteS10 » ;

- Étiquettes pour les dates des sorties : « EnteteDS01 », « EnteteDS02 », …,« EnteteDS10 ».

Procédure événementielle sur impression de l'entête de groupe

Private Sub EntêteGroupe0_Print(Cancel As Integer, PrintCount As Integer)
Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb

'Définition du sql permettant de sélectionner pour chaque produit, les mouvements des sorties comprises dans la période saisie en paramètre.
sSQL = "select tMouvementsPK, MvtDate from rSorties " & _
"where (tProduitsFK=" & [tProduitsFK] & ") and MvtDate between #" & Format(Forms!fParamPeriode!ParamDateDebut, "mm/dd/yyyy") & "# and #" & Format(Forms!fParamPeriode!ParamDateFin, "mm/dd/yyyy") & "# " & _
"order by tMouvementsPK;"

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) ' Ouverture du recordset

i = 1

If Not rs.EOF Then ' Si des sorties ont été trouvées dans cette période
Do While (i <= 10) ' On parcourt les entêtes
' Mise à jour des entêtes (identifiants et dates des sorties)
Me("EnteteS" & Format(i, "00")).Caption = rs!tMouvementsPK
Me("EnteteDS" & Format(i, "00")).Caption = rs!MvtDate
rs.MoveNext: i = i + 1 'Sortie suivante
If rs.EOF Then Exit Do
Loop
End If

Do While (i <= 10) ' On vide les entêtes supplémentaires
Me("EnteteS" & Format(i, "00")).Caption = ""
Me("EnteteDS" & Format(i, "00")).Caption = ""
i = i + 1
Loop

'Fermeture et libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Sub

VI-A-2 - Gros plan sur le pied de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'un pied de groupe dans lequel on peut afficher les valeurs du stock (Qté, PU, Total) pour chaque sortie de produit :

Pied de groupe de l'état

Formules employées pour calculer les différentes valeurs en sortie de stock suivant la méthode FIFO :

- QteSortiei :« =Somme([S0i]) » ;

- PUSortiei :« =Somme([S0i]*[PUEntree])/Somme([S0i]) » ;

- Totali :=« Somme([S0i]*[PUEntree]) ».

i désigne l'indice de la colonne de sortie, il est compris entre 1 et 10 dans notre cas.

VI-B - eLIFO

Tableau de synthèse de la valorisation des stocks (méthode LIFO) :

Valorisation du stock : méthode LIFO

Le tri se fait par produit et mouvement en entrée du stock.

VI-B-1 - Gros plan sur l'entête de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut afficher dans son entête les identifiants des mouvements en sortie (tMouvementsPK) pour chaque produit :

entête de groupe de l'état

On met à jour les entêtes de colonne du groupe au moyen d'une procédure événementielle exécutée sur Impression de l'entête de groupe.

entêtes de colonne

- Étiquettes pour les numéros des mouvements en sortie : « EnteteS01 », « EnteteS02 », …,« EnteteS10 » ;

- Étiquettes pour les dates des sorties : « EnteteDS01 », « EnteteDS02 », …,« EnteteDS10 ».

Procédure événementielle sur impression de l'entête de groupe

Private Sub EntêteGroupe0_Print(Cancel As Integer, PrintCount As Integer)
Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb

'Définition du sql permettant de sélectionner pour chaque produit, les mouvements des sorties comprises dans la période saisie en paramètre.
sSQL = "select tMouvementsPK, MvtDate from rSorties " & _
"where (tProduitsFK=" & [tProduitsFK] & ") and MvtDate between #" & Format(Forms!fParamPeriode!ParamDateDebut, "mm/dd/yyyy") & "# and #" & Format(Forms!fParamPeriode!ParamDateFin, "mm/dd/yyyy") & "# " & _
"order by tMouvementsPK;"

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) ' Ouverture du recordset

i = 1

If Not rs.EOF Then ' Si des sorties ont été trouvées dans cette période
Do While (i <= 10) ' On parcourt les entêtes
' Mise à jour des entêtes (identifiants et dates des sorties)
Me("EnteteS" & Format(i, "00")).Caption = rs!tMouvementsPK
Me("EnteteDS" & Format(i, "00")).Caption = rs!MvtDate
rs.MoveNext: i = i + 1 'Sortie suivante
If rs.EOF Then Exit Do
Loop
End If

Do While (i <= 10) ' On vide les entêtes supplémentaires
Me("EnteteS" & Format(i, "00")).Caption = ""
Me("EnteteDS" & Format(i, "00")).Caption = ""
i = i + 1
Loop

'Fermeture et libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Sub

VI-B-2 - Gros plan sur le pied de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'un pied de groupe dans lequel on peut afficher les valeurs du stock (Qté, PU, Total) pour chaque sortie de produit :

Pied de groupe de l'état

Formules employées pour calculer les différentes valeurs en sortie de stock suivant la méthode LIFO :

- QteSortiei :« =Somme([S0i]) » ;

- PUSortiei :« =Somme([S0i]*[PUEntree])/Somme([S0i]) » ;

- Totali :=« Somme([S0i]*[PUEntree]) ».

i désigne l'indice de la colonne de sortie, il est compris entre 1 et 10 dans notre cas.

VI-C - eCMUP

Tableau de synthèse de la valorisation des stocks (méthode CMUP) :

Valorisation du stock : méthode CMUP

Le tri se fait par produit et mouvement de sortie du stock.

VI-C-1 - Gros plan sur l'entête de groupe de l'état

On réalise dans l'état un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut afficher dans son entête les indices des mouvements en sortie pour chaque produit :

entête de groupe de l'état

On met à jour les entêtes de colonne du groupe au moyen d'une procédure événementielle exécutée sur Impression de l'entête de groupe.

entêtes de colonne

- Étiquettes pour les indices des mouvements en sortie : « EnteteS01 », « EnteteS02 », …,« EnteteS10 » ;

- Étiquettes pour les dates des sorties : « EnteteDS01 », « EnteteDS02 », …,« EnteteDS10 ».

Procédure événementielle sur impression de l'entête de groupe

Private Sub EntêteGroupe0_Print(Cancel As Integer, PrintCount As Integer)
Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb

'Définition du sql permettant de sélectionner pour chaque produit, les mouvements des sorties comprises dans la période saisie en paramètre.
sSQL = "select tMouvementsPK, MvtDate from rSorties " & _
"where (tProduitsFK=" & [tProduitsFK] & ") and MvtDate between #" & Format(Forms!fParamPeriode!ParamDateDebut, "mm/dd/yyyy") & "# and #" & Format(Forms!fParamPeriode!ParamDateFin, "mm/dd/yyyy") & "# " & _
"order by tMouvementsPK;"

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) ' Ouverture du recordset

i = 1

If Not rs.EOF Then ' Si des sorties ont été trouvées dans cette période
Do While (i <= 10) ' On parcourt les entêtes
' Mise à jour des entêtes (indices et dates de sorties)
Me("EnteteS" & Format(i, "00")).Caption = "S" & Format(i, "00")
Me("EnteteDS" & Format(i, "00")).Caption = rs!MvtDate
rs.MoveNext: i = i + 1 'Sortie suivante
If rs.EOF Then Exit Do
Loop
End If

Do While (i <= 10) ' On vide les entêtes supplémentaires
Me("EnteteS" & Format(i, "00")).Caption = ""
Me("EnteteDS" & Format(i, "00")).Caption = ""
i = i + 1
Loop

'Fermeture et libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Sub

VI-C-2 - Gros plan sur le pied de groupe de l'état

On réalise dans les états un regroupement des données par produit sur le champ ProduitsFK. On dispose donc d'une section de groupe dans laquelle on peut calculer les valeurs du stock (Qté, PU, Total) pour chaque sortie de produit :

Pied de groupe de l'état

Formules employées pour calculer les différentes valeurs en sortie de stock suivant la méthode CMUP :

- QteSortiei:« =Somme([S0i]) » ;

- PUSortiei:« =[PUCMUP] » ;

- Totali:« =Somme([S0i]*[PUCMUP]) ».

i désigne l'indice de la colonne de sortie, il est compris entre 1 et 10 dans notre cas.

VII - Les bases de données à télécharger

Les bases jointes sont au format mdb et accdb. La version accdb utilise le fichier ruban.xml pour afficher les boutons de commande sur le ruban.

VIII - Remerciements

Je tiens à remercier Claude Leloup pour m'avoir conseillé pour la réalisation de cet article, ainsi que pour sa relecture.