Query to Find Balance

[samirshah172]

Hello Forum,

Example of My Query Which Shows Loading Detail Against tblDO from tblLoading:

Do : Qty : Loaded
1 : 48.000 : 15.900
1 : 48.000 : 16.200
1 : 48.000 : 15.900
2 : 32.000 :
I Need a Column Which Writes Balance of Each DO
Do : Qty : Loaded : Balance
1 : 48.000 : 15.900 : 32.100
1 : 48.000 : 16.200 : 15.900
1 : 48.000 : 15.900 : 0.000
2 : 32.000 : : 32.000

How to Write SQl to Get Balance

Regards
Samir Shah

 

[arnelgp]

without unique key, you cant in a query. creating temp table, yes.

 

[samirshah172]

Do is unique key. Table1 has Do, DoQty field. Table2 has LoadingNo, Do, Loaded field. Where LoadingNo is a key in table2.

Thanks for the reply

 

[arnelgp]

does table2 has unique key (autonumber), if not i suggest making one and include this new field in your query. post the result of the query after you made the changes.

 

[samirshah172]

Yes it has key "LoadingNo" which is Auto number. let me try adding it. will submit soon.

 

[arnelgp]

SELECT T1.DO, T1.QTY, T2.Loaded, (T1.QTY - (SELECT SUM(T3.Loaded) FROM tblLoaded AS T3 WHERE T3.DO = T1.DO AND T3.LoadingNo <= T2.LoadingNo)) AS Balance FROM tblDO AS T1 LEFT JOIN tblLoading AS T2 ON T1.DO = T2.DO;

 

[samirshah172]

Thanks,

Its worked,

Regards
Samir