How does one use sql to find amount as a difference or two rows

[Jack]

eg.
A 100
B 50

Sql to get result as C 50 (This 50 is actually a calculation of 100-50).
Thanks in advance.

 

[Pendragon]

In a query format this would simply be C: [A]-. You can change the view
to SQL to see how that converts....

[A]- AS C

 

[krissco]

eg.
A 100
B 50

Sql to get result as C 50 (This 50 is actually a calculation of 100-50).
Thanks in advance.

I'm guessing that your real data is somewhat less trivial. You can
perform a sum - which will aggregate the rows - and modify the sign
based on a value in another row. Whew! Its a good thing I can provide
an example:

select sum(iif(SomeField = "A", 1, -1) * Amount) as Difference
from SomeTable

This will work for the trivial example. You will have to see if it
works for your real data.

-Kris