It would be possible if you can change your db's diagram like this：
ProductID，Rate，SaleAmount，PurchaseAmount
It is also possible without changing the db structure. But a better option would be to put the Purchase and sales in a seperate table. And I don't see why you shoudl use 2 columns, use a Positve Ammount for Purchase and a negative ammount for Sales
soft2creativ...
Member
21 Points
31 Posts
problem in Select Query in ms access
Jan 02, 2013 04:29 PM|LINK
I have a table contain products records table structure is
ProductID
Rate
Qty
Type
1
50
10
P
1
50
5
P
1
50
5
P
2
20
12
P
2
20
8
P
1
60
20
P
2
20
17
S
1
50
15
S
1
60
5
P
2
20
5
P
2
20
5
S
1
60
18
S
Table 1.1
In the Type column
Available qty=Purchase-sale
What should be the query to get the output as follow?
Product ID
Rate
Available Qty
1
50
5
1
60
7
2
20
3
Table 1.2
In the output Table 1.2 products are group by productid and rate.
Please tell me In sqlquery in MS-Access
Harsh Mittal
8053536111
9354891238
info@soft2creative.com
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: problem in Select Query in ms access
Jan 03, 2013 04:32 AM|LINK
Hello，
It would be possible if you can change your db's diagram like this：
ProductID，Rate，SaleAmount，PurchaseAmount
And then you can try this way：
Select ProductID，Rate，(PurchaseAmount-SaleAmount) As Quality From xxxTable
hans_v
All-Star
35986 Points
6550 Posts
Re: problem in Select Query in ms access
Jan 03, 2013 01:22 PM|LINK
It is also possible without changing the db structure. But a better option would be to put the Purchase and sales in a seperate table. And I don't see why you shoudl use 2 columns, use a Positve Ammount for Purchase and a negative ammount for Sales