access - query subtraction doubles values

Question

access - query subtraction doubles values

Navigation

#1 by (0 votes)

2

I coded the following queries:

SomaEntradasTtais:

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;

SomaSaidsTotal:

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 the StockTotal query is run containing containing only one article record, it works normally.

EX: Article: a1 - > Tickets: 10 - > Departures: 5 ------------ > Result of the Consultation: Article: a1 -> 5

However when I have 2 or more articles it adds the two and doubles to the two lines

EX:

Article: a1 - > Tickets: 10 - > Exit: 5
Article: a2 - > Tickets: 20 - > Exit: 5
Result of the Consultation: Article: a1 - > 40, Article: a2 - > 40

query ms-access

asked by anonymous 30.06.2016 / 11:07

1 answer

Inappbrowser using Phonegap build Sum of 2 inputs and appear in real time - Javascript

score 0 · Answer 1

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

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

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

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

The function Nz is used in this case to return value 0 (zero) when the value of the field is null.