IV.3. LES UNITES
LOGIQUES
IV.3.1. Quelques unités
des Entrées des données
a. Création du nouvel exercice
- Table : Exercice (CodeExercice, DescrAnnee)
- Formulaire
b. Saisie des Factures
- Table : OpFacture (NumOpFact, DateFact,
RefCmdCl, #CodeCl, #CodeExercice)
TransFacture (NumTransFact,
QteArtFact, PVUArtFact, ObsFact, #NumOpFact, #CodeArt)
- Formulaire
c. Saisie des Articles
- Table : Article (CodeArt, NomArt, PVUArt,
#CodeFourn, #CodeCat, #CodeDepot)
- Formulaire
IV.3.2. Quelques rapports
a. Facture
- Requête
- Code SQL
SELECT tClient.CodeCl, tClient.NomCl, tClient.AdrCl,
tClient.TelCl, tOpFacture.NumOpFact, tOpFacture.DateFact, tOpFacture.RefCmdCl,
tExercice.CodeExercice, tExercice.DescrAnnee, tTransFacture.NumTransFact,
tTransFacture.NomArt, tTransFacture.QteArt, tTransFacture.PVUArt,
tTransFacture.ObsFact, tTransFacture.Date, [QteArt]*[PVUArt] AS Total,
Sum([QteArt]*[PVUArt]) AS Somme, ([QteArt]*[PVUArt]*0)/100 AS Reduction
FROM (tExercice INNER JOIN (tClient INNER JOIN tOpFacture
ON tClient.CodeCl = tOpFacture.NomClient) ON tExercice.CodeExercice =
tOpFacture.CodeExercice) INNER JOIN tTransFacture ON tOpFacture.NumOpFact =
tTransFacture.NumOpFacture
GROUP BY tClient.CodeCl, tClient.NomCl, tClient.AdrCl,
tClient.TelCl, tOpFacture.NumOpFact, tOpFacture.DateFact, tOpFacture.RefCmdCl,
tExercice.CodeExercice, tExercice.DescrAnnee, tTransFacture.NumTransFact,
tTransFacture.NomArt, tTransFacture.QteArt, tTransFacture.PVUArt,
tTransFacture.ObsFact, tTransFacture.Date, [QteArt]*[PVUArt],
([QteArt]*[PVUArt]*0)/100;
- Etat
b. Fiche de stock
- Requête
- Code SQL
SELECT tDepot.CodeDepot, tDepot.NomDepot,
tArticle.CodeArt, tArticle.NomArt, tArticle.StokAlert, tCategorie.NomCat,
tOpStock.NumOpStock, tOpStock.DateOpStock, tTransStock.NumTransStock,
tTransStock.TypeOp, tTransStock.NomArt, tTransStock.QteArtEntr,
tTransStock.QteArtSort, tTransStock.CAUArtEntr, tTransStock.PVUArtSort,
tExercice.CodeExercice, Month([DateOpStock]) AS MoisJours,
MonthName([MoisJours]) AS NomMoi, [QteArtEntr]*[CAUArtEntr] AS
TotalEntree
FROM (tExercice INNER JOIN tOpStock ON
tExercice.CodeExercice = tOpStock.CodeExercice) INNER JOIN (tDepot INNER JOIN
(tCategorie INNER JOIN (tArticle INNER JOIN tTransStock ON tArticle.CodeArt =
tTransStock.NomArt) ON tCategorie.CodeCat = tArticle.NomCat) ON
tDepot.CodeDepot = tArticle.NomDepot) ON tOpStock.NumOpStock =
tTransStock.NumOpStock
WHERE (((tArticle.CodeArt)=[Entrez le code de l'article])
AND ((tExercice.CodeExercice)=[Entrez l'Exercice]) AND
((Month([DateOpStock]))=[Entrez le moi (En chiffre, Ex: 1 pour Janvier, 2 pour
Fevrier)]));
- Etat
c. Vente par client
- Requête
- Code SQL
SELECT tArticle.CodeArt, tArticle.NomArt, tClient.CodeCl,
tClient.NomCl, tClient.AdrCl, tClient.Photo, tExercice.CodeExercice,
tOpFacture.DateFact, tTransFacture.NumTransFact, tTransFacture.QteArt,
tTransFacture.PVUArt, tTransFacture.Date, [QteArt]*[PVUArt] AS Total
FROM (tExercice INNER JOIN (tClient INNER JOIN tOpFacture
ON tClient.CodeCl = tOpFacture.NomClient) ON tExercice.CodeExercice =
tOpFacture.CodeExercice) INNER JOIN (tArticle INNER JOIN tTransFacture ON
tArticle.CodeArt = tTransFacture.NomArt) ON tOpFacture.NumOpFact =
tTransFacture.NumOpFacture
WHERE (((tOpFacture.DateFact) Between [Date début]
And [Date fin]));
- Etat
d. Ventes par Catégorie
- Requête
- Code SQL
SELECT tArticle.CodeArt, tArticle.NomArt,
tCategorie.CodeCat, tCategorie.NomCat, tTransFacture.QteArt,
tTransFacture.PVUArt, [QteArt]*[PVUArt] AS Total
FROM tOpFacture INNER JOIN (tCategorie INNER JOIN
(tArticle INNER JOIN tTransFacture ON tArticle.CodeArt = tTransFacture.NomArt)
ON tCategorie.CodeCat = tArticle.NomCat) ON tOpFacture.NumOpFact =
tTransFacture.NumOpFacture;
- Etat
e. Variation des ventes par article
- Requête
- Code SQL
SELECT tArticle.CodeArt, tArticle.NomArt,
tCategorie.CodeCat, tCategorie.NomCat, tExercice.DescrAnnee,
tTransFacture.QteArt, tTransFacture.PVUArt, tTransFacture.Date,
[QteArt]*[PVUArt] AS Total
FROM (tExercice INNER JOIN tOpFacture ON
tExercice.CodeExercice = tOpFacture.CodeExercice) INNER JOIN (tCategorie INNER
JOIN (tArticle INNER JOIN tTransFacture ON tArticle.CodeArt =
tTransFacture.NomArt) ON tCategorie.CodeCat = tArticle.NomCat) ON
tOpFacture.NumOpFact = tTransFacture.NumOpFacture
WHERE (((tArticle.CodeArt)=[Entrez le code de
l'Article]));
- Etat
| 
