SELECT tblReagntList.rlID,
tblReagntList.rl_name,
IIf(IsNull(Sum(tblReagent_r.Count)),0,Sum(tblReagent_r.Count)) AS SumOf_r,
IIf(IsNull(Sum(tblReagent_r.Sum_page)),0,Sum(tblReagent_r.Sum_page)) AS SumOf_rb,
IIf(IsNull(Sum(tblReagent_o.Count)),0,Sum(tblReagent_o.Count)) AS SumOf_o,
SumOf_r-SumOf_o AS BL,
tblReagntList.lessthan
FROM (tblReagntList LEFT JOIN tblReagent_o ON tblReagntList.rlID = tblReagent_o.rrID) INNER JOIN tblReagent_r ON tblReagntList.rlID = tblReagent_r.rlID
GROUP BY tblReagntList.rlID, tblReagntList.rl_name, tblReagntList.lessthan;
SELECT
rl.rlID, rl.rl_name,
sum(coalesce(r.Count, 0)) AS SumOf_r,
sum(coalesce(r.Sum_page,0)) AS SumOf_rb,
sum(coalesce(o.Count,0)) AS SumOf_o,
SumOf_r-SumOf_o AS BL, # บันทัดนี้ น่าใช้ โค๊ดหลัก บวกเอา
rl.lessthan
FROM tblReagntList as rl
LEFT JOIN tblReagent_o as o ON rl.rlID = o.rrID
INNER JOIN tblReagent_r as r ON rl.rlID = r.rlID
GROUP BY rl.rlID, rl.rl_name, rl.lessthan;
SELECT tblReagntList.rlID, tblReagntList.rl_name,
(iif(isnull( (SELECT sum( tblReagent_r.Count) from tblReagent_r where tblReagent_r.rlID =tblReagntList.rlID )),0, (SELECT sum( tblReagent_r.Count) from tblReagent_r where tblReagent_r.rlID =tblReagntList.rlID )) ) AS SumOf_r,
(iif(isnull( (SELECT sum( tblReagent_r.Sum_page) from tblReagent_r where tblReagent_r.rlID =tblReagntList.rlID )),0, (SELECT sum( tblReagent_r.Sum_page) from tblReagent_r where tblReagent_r.rlID =tblReagntList.rlID )) ) AS SumOf_rb, (iif(isnull( (SELECT sum( tblReagent_o.Count) from tblReagent_o where tblReagent_o.rrID =tblReagntList.rlID )),0, (SELECT sum( tblReagent_o.Count) from tblReagent_o where tblReagent_o.rrID =tblReagntList.rlID )) ) AS SumOf_o, SumOf_r-SumOf_o AS BL, tblReagntList.lessthan
FROM tblReagntList;
Load balance : Server 01
