dimanche 1 mars 2015

Access Query Running Subtraction

I'm working on a project, in which I need to calculate all the due payment and to show in DataGridView. I've made a query in Query Design. But I'm unable to make query to get total, paid amount, due amount. Here is the query:



SELECT Payment.PayDate, OrderMaster.OrderId, OrderMaster.CustomerName, OrderMaster.TotalAmount, Payment.PayAmount, OrderMaster.TotalAmount - Payment.PayAmount AS DueAmount FROM OrderMaster INNER JOIN Payment ON OrderMaster.OrderId = Payment.OrderId;


I'm getting the query result like this:



PayDate | OrderId | CustomerName | TotalAmount | PayAmount | DueAmount
-------------------------------------------------------------------------
20/12/2014 | 1 | Someone | 5000 | 2000 | 3000
22/12/2014 | 1 | Someone | 5000 | 1000 | 4000
25/12/2014 | 1 | Someone | 5000 | 2000 | 3000


I want the query result like this:



PayDate | OrderId | CustomerName | TotalAmount | PayAmount | DueAmount
-------------------------------------------------------------------------
20/12/2014 | 1 | Someone | 5000 | 2000 | 3000
22/12/2014 | 1 | Someone | 5000 | 1000 | 2000
25/12/2014 | 1 | Someone | 5000 | 2000 | 0


Please help me to make the exact query to get this result. Thanks.


Aucun commentaire:

Enregistrer un commentaire