A Query that make opening closing balance and also show today movement in DB and CR

[yameen2011]

Hi friends I have issue while making a query kindly help me.
I want to perform in this query that
Opening balance + Dr - Cr = Closing balance
And the next day my opening balance will equal to the date() -1 closing balance. And when today’s movement DR and CR take changes it will also take affect on closing balance.
Here I have two questions 1st is how can I built a qry that will make today’s opening equal to last day closing . Can I have to built two queries or make another table that store last day closing then create relationship and again built a query that perform this If this is possible please tell me the solution this problem I will be thankful in this regard and much be appreciated
I have make a very long discussion to explain my issue I hope you all easily understand that what I am saying and what I am trying to perform , screenshot is attached that explain my issue.
Thanks in advance.

 

[plog]

I would do this with the DSUM function (http://www.techonthenet.com/access/functions/domain/dsum.php). I would use it twice, once to sum up all the Credits in all the dates before the current record's date and I would do the same for Debits. I would subtract the Debits from the Credits to determine that record's opening balance.

Then, for the closing balance, I would take the opening balance calculation, add that record's credits and subtract it's Debits.

There's really no query trickery involved, the key is getting the DSum criteria argument correct. If you need help, post sample data from your table. Be sure to include the table name and all relevant field names.

 

[yameen2011]

I would do this with the DSUM function (http://www.techonthenet.com/access/functions/domain/dsum.php). I would use it twice, once to sum up all the Credits in all the dates before the current record's date and I would do the same for Debits. I would subtract the Debits from the Credits to determine that record's opening balance.

Then, for the closing balance, I would take the opening balance calculation, add that record's credits and subtract it's Debits.

There's really no query trickery involved, the key is getting the DSum criteria argument correct. If you need help, post sample data from your table. Be sure to include the table name and all relevant field names.

Dear i have a tbl_General_Ledger fields are
GL_ID, GL_CODE, Description, DR, CR, TDate
1 1000100 Cash in hand 1000 0 24/04/2014
2 1000200 Cash in Bank 0 2000 24/04/2014
3 2500100 Fee Coll Main 0 500 24/04/2014
4 2500110 Admission Fee 0 100 24/04/2014
5 2500120 Exam Fee 0 150 24/04/2014
6 2500130 Monthly Fee 0 100 24/04/2014
7 2500120 Exam Fee 0 150 24/04/2014


This is a sample of my data in tbl_General Ledger. the data is appended here from two different tables one from GLTransactions Second from FeeCollection,everything is fine,but i want here in tbGeneralLedger that records are grouped by GL_code, and DR, CR, show sum of total DR, CR Entries and default date is current date().
GL_CODE 2500120 appear in tbl two times but i want in a query that show records grouped by GL_Code in ascending order and show me the opening and closing.
thanx in advance

 

[plog]

Getting closer. Based on the sample data you posted, show me what the data should result in.

 

[yameen2011]

Getting closer. Based on the sample data you posted, show me what the data should result in.

(show me what the data should result in?)
i don't understand your question, kindly explain your question.

 

[plog]

You posted this data:

Dear i have a tbl_General_Ledger fields are
GL_ID, GL_CODE, Description, DR, CR, TDate
1 1000100 Cash in hand 1000 0 24/04/2014
2 1000200 Cash in Bank 2000 24/04/2014
3 2500100 Fee Coll Main 500 24/04/2014
4 2500110 Admission Fee 100 24/04/2014
5 2500120 Exam Fee 150 24/04/2014
6 2500130 Monthly Fee 100 24/04/2014
7 2500120 Exam Fee 150 24/04/2014

You want a query to feed this data into. Let's assume that query is created, what does that query produce when you feed it that data? Show me more data, this time what you expect as a result.

 

[yameen2011]

dear if you look at my attachment file on top you can easily understand my requirements!

 

[plog]

I did look at it, then I replied to it with an explanation of how to accomplish that. You then posted again without referencing my initial post. I then responded what I would need to help you with your seemingly new issue. You asked for clarification, I replied, you told me to return to post #1.

Now we are here. Follow my instructions in post #6 if you would like me to help you further.

 

[yameen2011]

You posted this data:

Dear i have a tbl_General_Ledger fields are
GL_ID, GL_CODE, Description, DR, CR, TDate
1 1000100 Cash in hand 1000 0 24/04/2014
2 1000200 Cash in Bank 2000 24/04/2014
3 2500100 Fee Coll Main 500 24/04/2014
4 2500110 Admission Fee 100 24/04/2014
5 2500120 Exam Fee 150 24/04/2014
6 2500130 Monthly Fee 100 24/04/2014
7 2500120 Exam Fee 150 24/04/2014

You want a query to feed this data into. Let's assume that query is created, what does that query produce when you feed it that data? Show me more data, this time what you expect as a result.

Sorry for misunderstanding by me, dear i need your help
now i am attaching screen shot that will show you the results i want to create , in first screen shot after calculating debit - credits closing balance and closing also shows in opening on next day,and DR and CR are Null, and then in 3rd screen shot when more transactions are made then closing balance changes according to the transaction through GL codes,
and then after this my today's closing will be the opening balance for the next day and so on.

 

[plog]

Post starting data and ending data. Don't explain anything, don't try to hold my hand through the process. Give me sample starting data, then what data should be the result of that starting data.

Think of me as Google Maps. Input an origination point (starting data) and then a destination (what data should look like in the end) and I will make you a map between the two.

 

[yameen2011]

now i am giving you the sample of my database.
where when a fee deposited the Fee main GL Credited and cash is debited these are auto entries coming from Fee table, ok and now look at my query that showing closing balance for today. here i need another field in this query that represent today,s closing to opening on next day .hope now i share enough information to explaining my problem.

 

[yameen2011]

one thing more that this query show only today,s sum of debit and credit, and closing balance formula is (Opening + DR - CR = CLOSING)
because i want that closing hold closing balance not only for the current day, closing show me according to above formula.

 

[yameen2011]

one thing more that this query show only today,s sum of debit and credit, and closing balance, in case there is no transaction DR, CR will be Default null value 0.
formula is calculating for closing (Opening + DR - CR = CLOSING)
because i want that closing hold closing balance not only for the current day, closing show me according to above formula.
and first time when there is no transaction then opening and closing are null and = 0.

 

[yameen2011]

Dear plog i solved my problem.

 

[Mazhar.Iqbal]

Dear plog i solved my problem.

Salam, Dear Mr. Yameen, can us post the code how did you solve your problem, so that I and others may get benefit from. Thanks in advance.

 

[yameen2011]

Salam, Dear Mr. Yameen, can us post the code how did you solve your problem, so that I and others may get benefit from. Thanks in advance.

Dear Mazhar,
For doing this i have used a pakistani (Desi) method to built this, there is no programming required but you must have much knowledge of building queries.
first i make a table General_Ledger,
containing fields, id, GL_Code,Opening_Balance, Dr_Amt, Cr_Amt, TDate
and set default values of Opening_Balance, Dr_Amt, Cr_Amt to ZERO,
then make another table Hold_Closing, containing fields,
GL_Code,Closing_Balance,Tdate
and make a Append_query that calculate Sum of this Closing=Opening+Dr_amt-Cr_amt for current date.
and apend this to Hold_Closing table, and this query runs on everday at day end.
then make another append query Post_opening, this query append your last day closing from Hold closing table, and this query run on each day at morning, and now you make another query that show your opening closing and dR and Cr, entries, for current date,
Note:You have to run query once, if you run query twice then you will find wrong out put you have to build this very carefully,

One thing to remember, that i have found these concepts from a core banking system, where i am working, and there is no way to build this through any programming in ms-access, i have found nothing on internet , do it on your own requirement, and you must have to make Flags, like SOD (Start of day), and EOD (End of day) System in your database, if you need more help, You can conect with me by email or cell
skype:yameen.malix
yameen.malix@gmail.com
0333-5192031
0333-5192034