How do i...create the trail balance with opening balance and closing balance..closing balance will become opening balance on selected date range.from two tables one is account second is transection

Question

1.00/5 (1 vote)

See more:

Suppose I have two table same as above 
Accounts 
ACCTID	ACCTNAME	ACCT TYPE	Opening Balance	Date
12	CASH ACCT	DEBIT	1200	1-4-2023
13	BANK ACCT	DEBIT	1300	1-4-2023
14	SUPPLIER ACCT 1	CREDIT	1400	1-4-2023
15	SUPPLIER ACCT 2	CREDIT	800	1-4-2023
16	EXPESNES1	DEBIT	900	1-4-2023

Transactions Table
Accno	Acct Name	Acct Type	Debit amt	Credit amt	Date
12	CASH ACCT	DEBIT	5000	0	1-4-2023
12	CASH ACCT	DEBIT	1000	0	1-4-023
12	CASH ACCT	CREDIT	0	2000	1-4-2023
12	CASH ACCT	DEBIT	1000	0	2-4-2023
13	BANK ACCT	CREDIT	0	200	1-4-2023
13	BBANK ACCT	DEBIT	4000	0	2-4-2023
13	BANK ACCT	CREDIT	300	0	3-4-2023
14	SUPPLIER ACCT	CREDIT	0	400	1-4-2023
14	SUPPLIER ACCT	CREDIT	0	300	3-4-2023
15	SUPPLIER ACCT 2	DEBIT	2000	0	1-4-2023
15	SUPPLIER ACCT 2	CREDIT	0	1000	4-4-2023

I HVE TO DISPLAY VALUE LIKE THIS
Opening balance will show according to credit and debit acct type  for example
From date      : 2023-04-01                                                           to date  2023-04-02
Acct name	Credit opening balance	Debit opening balance	Credit(sum)	Debit(sum)	Closing credit	Closing debit
Casah acct	0	1200	2000	6000	0	5200
Bank acct	0	1300	600	4000	0	4700
Supplier acct	1400	0	700	100	2200	0
Supplier acct2	800	0	2000	2000	1800	
						

Opening balance will show debit side and credit side according to account type 
And opening balance will be change according to date  . I mean closing balance will become to opening balance on next date
I have to display the record according to date range wise (date between wise )
Please help me in this situation I will highly  thank full to you

What I have tried:

i need help please to get the solution please

Posted 4-Apr-23 16:50pm

samhasan

Updated 4-Apr-23 17:45pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Answer 1 · 2023-04-04T17:45:00

Solution 1

Debit or credit is a type of balance; not an "account".

Account types include: Assets and liabilities; short term (e.g. cash; payables) or longterm (loans).

A certain account may tend to have a debit or credit balance, but it can also go the other way; e.g. overdrawn; turning a debit balance into a credit balance.

The sum of ALL debits and credits before a given date is the "opening balance" for that date (for that account); regardless of the sign of the balance.

Posted 4-Apr-23 17:45pm

Gerry Schmitz

Comments

samhasan 5-Apr-23 10:33am

Hi..Thanks for your respon..i share over a sample schema with data..acct type to just show its creditor or debitor account ..from one table which is called transection table i can find the all details which i need..but when its show closing balance at dr side it should show closing balance at cr side o same as opening balance ..i tried to clearfy may be you got it..thanx
so please help me with some example with sql query (using two tables (one table have only opening balance) seconfd table with have crdit and debit amount for each account ..thanx