Click here to Skip to main content
15,507,372 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
How to calculate difference of purchase and Sale quantity in One Query Using Ms Access Database?

For Ex:
ProductId Type Quantity
1 Purchase 20
1 Sale 10

How to get difference of (20-10=10)in one Query?
Updated 16-Sep-13 0:37am
Maciej Los 16-Sep-13 5:31am    
The question is: how to help you without any background information?

1 solution

Something like this:
SELECT P1.ProductId, (P1.Quantity - P2.Quantity) FROM MyProds as P1 INNER JOIN  MyProds as P2 ON P1.ProductId=P2.ProductId WHERE P1.Type='Purchase' AND P2.Type='Sale';

should do the trick.
Share this answer
Maciej Los 16-Sep-13 5:58am    
Well done!
CPallini 16-Sep-13 6:02am    
Thank you.
CPallini 16-Sep-13 7:42am    
[Restoring the deleted, by mistake, OP comment]
It Does Not give me desired result,It Show me wrong value
i have a 24 purchase qty and 1 sale qty
It give me a result 21 and 1
NABIN SEN - 58 mins ago
CPallini 16-Sep-13 7:43am    
It works for me on the example you provided. Could you please post here the exact set of inputs and ouputs?
NABIN SEN 16-Sep-13 7:49am    
ProductId Type Quantity
1 Purchase 24
1 Sale 1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900