2
I coded the following queries:
Somantradastotal:
SELECT artigos.Nome, Sum(Entradas.quantidade_ent) AS [Total Entradas]
FROM artigos INNER JOIN Entradas ON artigos.Cod_artigo = Entradas.Cod_artigo
GROUP BY artigos.Nome;
Somasaidastotals:
SELECT Artigos.Nome, Sum(Saidas.quantidade_sai) AS [Total Saidas]
FROM Artigos INNER JOIN Saidas ON Artigos.Cod_artigo = Saidas.cod_artigo
GROUP BY Artigos.Nome;
Stocktotal:
SELECT Artigos.cod_artigo, Artigos.Nome, Sum([SomaEntradasTotais]![Total Entradas]-[SomaSaidasTotais]![Total Saidas]) AS Stock
FROM Artigos, SomaEntradasTotais, SomaSaidasTotais
GROUP BY Artigos.cod_artigo, Artigos.Nome;
When consulting Stocktotal is executed containing only one record of articles, works normally.
EX: Article:a1 --> Entries:10 --> Exits:5 -----------> Result of Query: Article:a1 -->5
However when I have 2 or more articles it adds the two and doubles for the two lines
EX:
- Article:a1 --> Entries:10 --> Exits:5
- Article:a2 --> Entries:20 --> Exits:5
- Result of query: Article:a1 --> 40, Article:a2 --> 40