access - subtraction in query duplicates values

Question

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

Answer 1

@demolion For the values not to double, it is necessary to realize a LEFT JOIN between your table of Artigos and the tables of SomaEntradasTotais and SomaSaidasTotais, as below:

FROM (Artigos 
LEFT JOIN SomaEntradasTotais ON Artigos.Nome = SomaEntradasTotais.Nome) 
LEFT JOIN SomaSaidasTotais ON Artigos.Nome = SomaSaidasTotais.Nome

I also suggest you change the field expression Stock, including the function Nz:

Sum(Nz([SomaEntradasTotais]![Total Entradas],0)-Nz([SomaSaidasTotais]![Total Saidas],0)) AS Stock

The function Nz is then used to return value 0 (zero) when the field value is null.