Query - TotalSalesAmt - TotalPurchaseAmt = Profit/Loss (1 Viewer)

[sundaram]

Dear Sirs,

SELECT TblTran.ItCode, TblTran.Item, TblTran.TUnit, Sum(TblTran.TQty) AS SumOfTQty, Avg(TblTran.TRate) AS AvgOfTRate, [sumofTqty]*[AvgofTrate] AS TotalAmt, TblTran.TTranscatType
FROM TblTran
GROUP BY TblTran.ItCode, TblTran.Item, TblTran.TUnit, TblTran.TTranscatType;


I have the table TblTran
Fields are ItCode, Item, Tunit,TQty,TRate and TTranscatType

Now question is that I need to get Profit or Loss with alias TotalSalesAmount and TotalPurchaseAmount.

Please help me out.

Thanks in advance.
RAJAMALLU

 

[apr pillai]

You can use the above Query as source along with the Purchase Table joined on common ItemCode (.itCode)field and select all the fields from the Query and Purchase Value from the Table to calculate Profit/Loss.