I Got 2 tables: 'Standby' and 'Usage'
Standby Table got this fields:
PartNº (equipment id);
Area (physical location);
Stock1 (Nº Total of equipments);
Usage table got this fields:
Quant_Used (The times that the equipement is taken from Standby);
Quant_Received (The times that the equipement is restored to Standby);
PartNº (foreign key);
Standby
PartNº--Area--Stock1
0001----ABC----4
0002----AGC----1
0003----ABC----5
0004----AIC----3
Usage
PartNº--Quant_Used--Quant_Received
0001------1------1
0001------2------1
0003------1------1
0003------4------1
With this code:
Select Standby.PartNº,Standby.Stock1, Standby.Stock1 - (SUM([Usage].Quant_Used)-SUM([Usage].Quant_Received)) as [No Exterior], Standby.Area from [Usage] RIGHT JOIN Standby ON [Usage].PartNº=Standby.PartNº GROUP BY Standby.PartNº,Standby.Area,Standby.Stock1
I got this result:
PartNº----Stock1----No Exterior----Area
001---------4 ---------3----------ABC
002---------1 --------------------AGC [No Exterior] no result
003---------6 ---------2----------ABC
004---------3 --------------------AIC [No Exterior] no result
But i want:
PartNº----Stock1----No Exterior----Area
001---------4 ---------3----------ABC
002---------1 ---------1----------AGC
003---------6 ---------2----------ABC
004---------3 ---------3----------AIC
I also tried ISNULL():
Select Standby.PartNº,Standby.Stock1, Standby.Stock1 - (SUM(ISNULL([Usage].Quant_Used,0))-SUM(ISNULL([Usage].Quant_Received,0))) as [No Exterior], Standby.Area from [Usage] RIGHT JOIN Standby ON [Usage].PartNº=Standby.PartNº GROUP BY Standby.PartNº,Standby.Area,Standby.Stock1
But i got a error:
Was used an incorrect number of arguments to a function in query expression 'Standby.Stock1 - (SUM(ISNULL([Usage].Quant_Used,0))-SUM(ISNULL([Usage].Quant_Received,0)))'.
Thanks for helping