Topic: Problem with calculated field
Hello Dmitry,
I hope you can help. I am using a calculated field (medicine.stock) to show, for each drug in my 'medicines' table, the (sum of drugs in) - (sum of drugs out). This is the calculation:
(select sum(inwards.inqty)
from inwards
where medicine.id = inwards.id_medicine)
-
(select sum(out.outqty)
from out
where medicine.id = out.id_medicine)
This works as I want but ONLY after I have received some drugs ('inwards' table) and despatched some drugs ('out' table) - see rows 1-5 in the attached screen-shot (medicinechest1.jpg). But if I have only received drugs and NOT despatched any, the calculation is not working - see rows 6-7 (medicinechest1.jpg). I need to do some test for zero but I cannot get the syntax right. Any ideas?
I'm using Windows XP and MVD 2.3 beta.
Thanks,
Derek