Hello Everybody,
i have created a query in access. query is below
SELECT Br.BranchName, SI.Vehicle, SI.PartNo, SI.Description, IIf(IsNull((SELECT SUM(OB) FROM [StockItemOB] where BranchName=Br.BranchName AND PartNo=SI.PartNo)),0,(SELECT SUM(OB) FROM [StockItemOB] where BranchName=Br.BranchName AND PartNo=SI.PartNo)) AS OB, IIf(IsNull((SELECT SUM(Qty) FROM [PurchaseTransaction] where BranchName=Br.BranchName AND PartNo=SI.PartNo)),0,(SELECT SUM(Qty) FROM [PurchaseTransaction] where BranchName=Br.BranchName AND PartNo=SI.PartNo)) AS Pur, IIf(IsNull((SELECT SUM(Qty) FROM [StockTransfer] where DestinationBranch=Br.BranchName AND PartNo=SI.PartNo)),0,(SELECT SUM(Qty) FROM [StockTransfer] where DestinationBranch=Br.BranchName AND PartNo=SI.PartNo)) AS StkIn, IIf(IsNull((SELECT SUM(Qty) FROM [StockTransfer] where SourceBranch=Br.BranchName AND PartNo=SI.PartNo)),0,(SELECT SUM(Qty) FROM [StockTransfer] where SourceBranch=Br.BranchName AND PartNo=SI.PartNo)) AS StkOut, IIf(IsNull((SELECT SUM(ET.Quantity) FROM [EstimateMaster] AS EM INNER JOIN EstimateTransaction AS ET ON EM.ID = ET.EstimateID where EM.BranchName=Br.BranchName AND ET.PartNo=SI.PartNo)),0,(SELECT SUM(ET.Quantity) FROM [EstimateMaster] AS EM INNER JOIN EstimateTransaction AS ET ON EM.ID = ET.EstimateID where EM.BranchName=Br.BranchName AND ET.PartNo=SI.PartNo)) AS Sal, IIf(IsNull((SELECT AVG(ET.Rate) FROM [EstimateMaster] AS EM INNER JOIN EstimateTransaction AS ET ON EM.ID = ET.EstimateID where EM.BranchName=Br.BranchName AND ET.PartNo=SI.PartNo)),0,(SELECT AVG(ET.Rate) FROM [EstimateMaster] AS EM INNER JOIN EstimateTransaction AS ET ON EM.ID = ET.EstimateID where EM.BranchName=Br.BranchName AND ET.PartNo=SI.PartNo)) AS Rate
FROM StockItem AS SI, Branch AS Br
ORDER BY SI.Vehicle, SI.PartNo;
From above query when i have created another query, then i m getting error . query is below
SELECT ViewItemStock2.BranchName, ViewItemStock2.Vehicle, ViewItemStock2.PartNo, ViewItemStock2.Description, ViewItemStock2.Sal
FROM ViewItemStock2
where ViewItemStock2.Sal >0
order by ViewItemStock2.Vehicle, ViewItemStock2.PartNo