Sum request of empty fields (despite using Nz)

benboyfr Posted messages 1 Registration date   Status Member Last intervention   -  
 castours -
Hello;

Beginning with Access 2013 (well, Access in general) I’m stuck on a query.
I’m trying to calculate a remaining number of pieces with a formula like: remaining = initial - out + restock.

But for that I’m trying to calculate "out" as the sum of all my pieces that left. I use in my query a calculated field of the type: "QS: Nz(Sum([Sortie_echantillonnage_table]![Quantité]);0)" because there may be no pieces out yet for a stock. It works fine until I want to group by Stock No ... and then the query returns empty fields (so I cannot perform my final calculation).

Here is the SQL code if it helps:

SELECT Mise_en_stock_echantillonnage_Table.ID, Nz(Sum([Sortie_echantillonnage_table]![Quantité]),0) AS QS
FROM (Mise_en_stock_echantillonnage_Table INNER JOIN Sortie_echantillonnage_table ON Mise_en_stock_echantillonnage_Table.ID = Sortie_echantillonnage_table.[N° ID]) INNER JOIN Reintegration_echantillonnage_table ON Mise_en_stock_echantillonnage_Table.ID = Reintegration_echantillonnage_table.[N° ID]
GROUP BY Mise_en_stock_echantillonnage_Table.ID;

I’m not sure I’m clear (it's not easy to explain ...)

Thanks in advance for your help.

2 answers

  1. tessel75
     
    Hello, "It works fine until I want to group by Stock No... and then the query returns empty fields (so I can't perform my final calculation)"
    I don’t understand, neither what you want to achieve as results nor what you actually get that bothers you.
    Otherwise, and if that could solve your request, you can always write your QS field
    QS = Sum(Nz()) instead of Nz(Sum())
    0
  2. castours
     
    Hello
    Are all your calculation fields numeric?
    0