Calculate

[gatarossi]

Dear all,

In my table, I have this fields:

item_code type quantity
xxxx1 IST 100
xxxx1 INV -300
xxxx1 FST 200

Then I need to do a consult that brings this:

= (FST) + (INV*-1) - (IST)
= (200) + (-300*-1) - (100)
= 400

How can I do it?

Thanks in advance!

Andre.

 

[Douglas J. Steele]

Is it fair to say that you want to add FST, add INV times -1 and add IST
time -1? (that's a slight restatement of your post, but at least for that
one case, it amounts to the same thing) If so, add another table that
indicates which fields need to be multiplied by -1:

Type Multiplier
IST -1
INV -1
FST 1

You can then join that table to your existing table, and create a query
along the lines of:

SELECT table1.item_code, Sum(table1.quantity * table2.multiplier) As Total
FROM table1 INNER JOIN table2
ON table1.type = table2.type
GROUP BY table1.item_code

 

[gatarossi]

Dear Doug,

Thanks a lot!!!

André.