Report with Opening and Closing Balance

[aliikhlaq2006]

I wand a query in which the closing balance automatically transfer to the opening balance with the start of the new month, so query become Opening balance+Advance Taken-Advance payback = closing balance.
For this i have attached the file with this post.

 

[CJ_London]

you shouldn't be storing balance as a value, but calculate it as and when required in a query

It would also be easier if you combined your taken and payback columns as one, using negative values for one or the other.

You would use a subquery to calculate the balances - assuming per employee, something like

SELECT Advance.EmployeeID, Format([Date],"mmm yy") AS Expr1, Advance.Taken, Advance.PayBack, (SELECT Sum(taken-payback) FROM advance T where employeeid=advance.employeeid and [date]<=advance.date) AS Balance
FROM Advance
ORDER BY Advance.EmployeeID, Advance.Date

Also note that Date is a reserved word. Using it as a field name can and will cause issues down the line

 

[Mark_]

Normally the only time you would need to save a starting or ending balance is if the underlying data is subject to change AND you need to record what was submitted as of a specific time.

IF this is a value that is reported to regulatory agencies I can understand saving it. If it is not, CJ_London's approach will make sure your numbers are always correct especially after entering adjustments or correcting data.

 

[aliikhlaq2006]

you shouldn't be storing balance as a value, but calculate it as and when required in a query

It would also be easier if you combined your taken and payback columns as one, using negative values for one or the other.

You would use a subquery to calculate the balances - assuming per employee, something like

SELECT Advance.EmployeeID, Format([Date],"mmm yy") AS Expr1, Advance.Taken, Advance.PayBack, (SELECT Sum(taken-payback) FROM advance T where employeeid=advance.employeeid and [date]<=advance.date) AS Balance
FROM Advance
ORDER BY Advance.EmployeeID, Advance.Date

Also note that Date is a reserved word. Using it as a field name can and will cause issues down the line

i think you should make a query in the database which i attached and send me that because i am trying but no success

 

[mike60smart]

Hi

See the qrybalance in the attached using the SQL provided by CJ

View attachment Database3.zip

 

[CJ_London]

i think you should make a query in the database which i attached

that is what I did - all you needed to do was copy and paste it

 

[aliikhlaq2006]

that is what I did - all you needed to do was copy and paste it

Yes i do the same thing but not worked but the file he send me working in that may be i have put that in my huge database which carries many tables and queries. Overall thank you both you guys.
I just need another favor can you please make this query explain to me step wise

 

[CJ_London]

the subquery

(SELECT Sum(taken-payback) FROM advance T where employeeid=advance.employeeid and [date]<=advance.date) AS Balance


adds up taken-payback for all records for the specified employee for the record in the main query and the date is less than or equal to the date in the record in the main query (identified in bold).

Since the subquery is using the same source as the main query it needs to be aliased so that it can be identified separately - hence the T, identified in Red. Could just as easily used something else and perhaps more meaningful such as 'Amounts' but since it is not referenced again, there seems little point