I have created a query as under :
SELECT A.ProductID, Sum(IIf(IsNull(A.Trans),0,A.Trans)) AS OP, B.TRN, B.RTN
FROM LogDetail AS A LEFT JOIN (SELECT ProductID, Sum(IIf([Action]="TR",[Trans],0)) AS TRN, Sum(IIf([Action]="RT",[Trans],0)) AS RTN FROM LogDetail WHERE LocationID=[MyLocation] AND...