Accounting Trial Balance Query (report) (1 Viewer)

mhakim

New member
Local time
Today, 14:02
Joined
Jan 25, 2021
Messages
3
Query contain
(1-openingDebit or 2-openingCredit) - (3-Debit and 4-Credit ) (5-endbalanceDebit and 6-endbalanceCredit)

First 1 and 2 columns are opening Balance (Debit or credit)

Second 3 and 4 columns are Transactions (Sum debit and sumcredit) with in period

Third 5 and 6 columns are ending balance (Debit or credit)

tables overview
1 table for heading JV Number & date and REF
2 table for transactions Account Number & debit & credit & description

thanks
 

WAB

New member
Local time
Today, 18:02
Joined
Feb 18, 2021
Messages
6
Hello mhakim
i am not quite clear what you want to achieve to have opening balances in the same table as the transactions does not to seem right. The opening balances and closing balances are usually computed for a period (like month or year end). This is just a query on your transactions table. Or do you mean you have 3 tables: 1 opening bal, 2nd transactions, 3rd ending balances? Whats JV number?
Greetings
WAB
 

mhakim

New member
Local time
Today, 14:02
Joined
Jan 25, 2021
Messages
3
i will give example

if i have HSBC BANK ACCOUNT

on -01-01-2019 deposit 300,000 DEBIT
on -30-06-2019 withdrawal 50,000 CREDIT
______________END 2019
on -01-01-2020 Deposit 200,000 DEBIT
on -30-06-2020 withdrawal 100,000 CREDIT
on -31-12-2020 Deposit 500,000 DEBIT

trial balance from 01-01-2020 to 31-12-2020 will be like that

first line for more Details
Description befor 01-01-2020 balance period Transactions from 1-1-2020 to 31-12-2020 Balance at 31-12-2020


Report Final Design :

Accname openingDebit openingCredit transactionsDebit TransactionCredit endDebit endCredit
_________________________________________________________________________________________________________________________________________________________
HSBC 250,000 zero 700,000 100,000 850,000 zero
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:02
Joined
Sep 12, 2006
Messages
14,220
I would actually do this

treat the opening balance as just another transaction, but give it an identifying ID type
treat transactions as plus/minus depending on whether they are debit/credit. Give them identifying ID types as well.
(the transaction types should be part of your entry process, not added after the event)
you get the closing balance by summing all the transactions.

personally I would not distinguish between debits and credits when allocating a posting type. I would just have a single "transaction type (in your case 3) and just treat it as plus or minus. It's more natural with computerised bookkeeping than actually arranging for separate debit and credit columns. The same with the opening and closing balances. Just plus and minus. I actually would not store the closing balance.

at the month or year end, you store a new opening balance by summing all the transactions so far. To be accurate you clear P&L accounts to zero, and transfer the total of all the P&L accounts to a profit summary account. (which maintains the double entry) The BS accounts just get a b/f total. The b/f totals of ALL the accounts should sum to zero.

For a TB report, you can put the values into different columns depending on your preferences and the transaction type.

A TB QUERY for the whole organisation should always just sum to zero, which is proof that your transactions are in principle correct.

Your examples
on -01-01-2019 deposit 300,000 DEBIT
on -30-06-2019 withdrawal 50,000 CREDIT
______________END 2019
on -01-01-2020 Deposit 200,000 DEBIT
on -30-06-2020 withdrawal 100,000 CREDIT
on -31-12-2020 Deposit 500,000 DEBIT

given this you have these postings (I put these in language code marks to format them nicely)
Code:
01/01/19    Deposit       300000 (ID type 3)
30/06/19    Withdrawal    -50000 (ID Type 4)

01/01/20    Balance b/f   250000 (ID Type 1) 'a year end process generates the bf ready for the next year

20/06/20    Withdrawal   -100000 (ID Type 3)
31/12/20    Deposit       500000 (ID Type 4)

01/01/21    Balance b/f   650000 (ID Type 1) 'a year end process generates the bf ready for the next year

For the year 2021, you will sum all transactions (including the bal b/f) between 1/1/21 and 31/12/21


Re your final line
HSBC 250,000 zero 700,000 100,000 850,000 zero
I don't understand your final total line. How do you get 700000 and 850000? Surely it's 500000 and 650000 in your terms.
 
Last edited:

arnelgp

error reading drive A:
Local time
Today, 19:02
Joined
May 7, 2009
Messages
11,462
your sample trial balance as per your final design.
 

Attachments

  • TrialBalance.accdb
    596 KB · Views: 5

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:02
Joined
Feb 19, 2002
Messages
31,026
I don't want to discourage your development efforts but QuickBooks is a real bargin for what you get. You will never be able to recreate it in Access for less than $50,000 and that is probably low. If you need custom processing, buy the ODBC driver so you can create an Access application that links to Quickbooks.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:02
Joined
Sep 12, 2006
Messages
14,220
I don't want to discourage your development efforts but QuickBooks is a real bargin for what you get. You will never be able to recreate it in Access for less than $50,000 and that is probably low. If you need custom processing, buy the ODBC driver so you can create an Access application that links to Quickbooks.

I wrote a really good NL/General Ledger with lots of stuff such as automatic reversing journals, in about 2 days. I use it for my own accounts. For information, the NL "Account Numbers" are strings, (even if they are wholly numeric) so can be extended to any length in sections of the NL where more detailed analysis is required. Far better to use strings, rather than actual numbers in my opinion. You will need some experience in designing a NL coding structure. You can use any codes you want, but if you have experience, you will end up with a better structure, I mean.

I decided not to take it further, so there's no SL (Accounts Receivable) / PL (Accounts Payable), but it could well be useful as a learning tool. I should think a good SL and PL would take about the same time, maybe a little longer. IMO most systems don't provide good enough payment matching to match payments to invoices (for Accounts receivable), and good enough payment selection routines (for Accounts payable) so that's where it's worth spending time.

I can't see a way to send emails or PMs (is a private conversation the same thing) - but if there is a way, and people are happy to send me an email, I will be more than happy to send out the full version. It's A2003, and it will work on any version of Access.
 

Users who are viewing this thread

Top Bottom