Solved Running sum of query, report with opening and Closing balance

[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...

 

[Pat Hartman]

Once you get the rest of the stuff fixed, the easiest solution will be a union query.

Create a totals query that sums the values up to the starting date.
Create a details query for the beginning through ending dates.

Union the two queries and use the Union as the RecordSource for the report.

If you want to display the data in two different fields, you don't need to store the data as two fields. You can use an expression to put the value in one column or the other. Also, I would only show 0 if the amount is 0. The other field should always be null. The zeros are "noise" and I find them disturbing since they have no meaning. You might have a Credit of 0 or a Debit of 0. Using your current layout, you would show 0 in both columns.

 

[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?