Net of 2 fields...

[suverman]

Hello, I have a table like

Code A B


3456 40 20
4587 10 50
5435 100 20
3456 50 100

By running a query I should get...

Code A B
3456 0 30
4587 0 40
5435 80 0

Basically the net to be shown wherever it is positive.
Thank you.

[NTC]

how is it that the first line is 30? in one line you subtract A from B, in the next line you subtact B from A.

confusing math.....

[suverman]

how is it that the first line is 30? in one line you subtract A from B, in the next line you subtact B from A.

confusing math.....

Ah, actually the first line is the consolidation of the first and the last record, whcih has the same code. The over-all effect is a 30 in B.

[suverman]

Its more like

In column A
if(A>B, A-B, 0)

In column B
if(B>A, B-A,0)

[Rhino373]

I can't see how to do this without to queries. You can do it a couple ways but an example would be the first would be a totals query grouping by Code and sum of the other two. The second query would be based off the first using the exact IF statements you made. so column A would be like "ColumnA: IIF(A>B, A-B, 0)" and B of course would be the other with the Code field as well.