Solved Running sum of query, report with opening and Closing balance (1 Viewer)

[iaasiqbal]

I have maintain to voluntary organization fund, so i have an access database . i need running sum of GL query1. and need sunning sum with opening and closing balance of GL query2 and report GL query2 . please help.

 

[arnelgp]

how can you compute the Total of a period when, the "period" is not saved on the table.
a date cannot guarantee that it is on "same" period, like in the case of accruals.

add another field signifying the Month/Year (period) of which the transaction belongs.
then you can summarized by this field.

 

[iaasiqbal]

i want to get data with date range

 

[plog]

First you need to fix your table structure.

1. Debits and Credits should be in the same field--[Amount]. Then you either simply use positive (for credits) or negative (for debits) numbers in that field. Or you could also add another field [Type] where you could store whether it is a credit or debit.

2. Date is a reserved word and makes coding/querying that much harder. Instead you should prefix that field name with what the date represents, e.g. Transaction_Date, GL_Date, etc.

Then what you are after is a called a 'running sum query'. Search the forum for that term--its been solved many times.

 

[CarlettoFed]

In the attachment you will find an example.
However, the first thing to do is learn how to manage the properties of the various fields of the tables and in particular:

• Size (if the type is Text, don't always leave 255 set as you almost always won't need all those fonts (this is to avoid wasting disk resources)
• Mandatory (if set to Yes it does not allow you to save the record if the requested data has not been entered)
• Allow zero length (to be set to No if data is always required)
• Indexed (set to "Yes (duplicates are not allowed)" when you want a value not to be entered more than once in a field and therefore to be unique (if you need to use two or more fields to avoid duplication).

etc...

 

[arnelgp]

use this form to input your date range.

 

[iaasiqbal]

use this form to input your date range.

Thanks for you support.
I am new in access and studying on it. I try to add balance to report as running sum but fail. Can I request you to add running balance to the file that you work. Thank you.

 

[iaasiqbal]

thanks all i have done the problem finally. thanks all again.

 

[arnelgp]

sorry, totally forget this one.
here, if it is not too late, check and try.

 

[zolkiflee]

I have maintain to voluntary organization fund, so i have an access database . i need running sum of GL query1. and need sunning sum with opening and closing balance of GL query2 and report GL query2 . please help.


View attachment 102332

View attachment 102333


View attachment 102334

if given set of numbers 551231046777, how do i convert the first 6 digits to date format as yy/mm/dd and dd/mm/yy
Please help thank you

 

[arnelgp]

CDATE(LEFT(theText,2) & "-" & MID(theText,3,2) & "-" & MID(theText,5,2))

 

[gemma-the-husky]

Seriously, I would avoid trying to manage a running sum in a query.

Try to work with an opening balance, a closing balance, and the transaction total (ie - the difference)
Data in a RDB doesn't work in a way that easily processes a running balance, and it's easier to do what the database IS good at.

eg -why do you think the running balance is critical?