Calculate Difference

[zyus]

I hv this sample report that sorted by MIA

MIA Bal Diff
1 100 200-100=100
2 200 500-200=300
3 500

Q. How to calculate difference of bal amount by MIA as per Diff as above...

Thanks

 

[scubadiver]

Give this a go

SELECT MIA, Diff AS New,
(SELECT Max([Total]) FROM table AS temp
WHERE temp.mia=table.mia-1) AS Old, ([bal]-[old]) AS diff
FROM table