Solved Accounting Trial Balance Query (report) (1 Viewer)

mhakim

Member
Local time
Today, 14:27
Joined
Jan 25, 2021
Messages
66
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
 

Ranman256

Well-known member
Local time
Today, 07:27
Joined
Apr 9, 2015
Messages
4,337
Make a query with the needed fields.
Sum if needed.
 

WAB

New member
Local time
Today, 18:27
Joined
Feb 18, 2021
Messages
9
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

Member
Local time
Today, 14:27
Joined
Jan 25, 2021
Messages
66
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:27
Joined
Sep 12, 2006
Messages
15,613
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:27
Joined
May 7, 2009
Messages
19,169
your sample trial balance as per your final design.
 

Attachments

  • TrialBalance.accdb
    596 KB · Views: 290

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:27
Joined
Feb 19, 2002
Messages
42,970
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:27
Joined
Sep 12, 2006
Messages
15,613
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:27
Joined
Feb 19, 2002
Messages
42,970
I wrote a really good NL/General Ledger with lots of stuff such as automatic reversing journals, in about 2 days.
I'm sure your app is terrific but it is NOT QuickBooks and does not have anywhere near the features of QuickBooks. Even if the poster could write the app in two days which I seriously doubt, QuickBooks would still probably be cheaper. QB is one of the best software values ever produced.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Feb 19, 2013
Messages
16,553
don't disagree, but as with any off the shelf software, it probably only does 80% of what you actually want. I haven't done it for a while so things may have improved but for a while I made a good living providing bolt on solutions (using access) around sales invoicing, stock control and management information reporting as well as automating importing/exporting of data from/to multiple sources.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Sep 12, 2006
Messages
15,613
I'm sure your app is terrific but it is NOT QuickBooks and does not have anywhere near the features of QuickBooks. Even if the poster could write the app in two days which I seriously doubt, QuickBooks would still probably be cheaper. QB is one of the best software values ever produced.

All I said was I had a free open code NL/GL application for anyone interested.

I did not say @mhakim (the OP) could write this in 2 days. I said I wrote it in about 2 days, but this is based on a lifetime of working with accounts and trial balances. Given the way the OP had asked his original question, I thought his data structure might not be optimum, and offered a working solution, or at least a starting point for him.

I would be interested to know of any particular NL/GL features that QB, Sage, or any other accounting application has, or needs, that I don't already have, or couldn't incorporate.

eg What is a general ledger and why is it important? - QuickBooks (intuit.com)

I know that volume sales make a lot of difference. If QB sell 100,000 copies, and I expect they sell many more than that, and put $1 of each into R&D, that's a lot of dollars going into improving the product, designing help files and so on - but better functionality. I really am not so sure.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:27
Joined
Feb 19, 2002
Messages
42,970
Dave, You're missing the point. QB can be purchased for ~ $250. How much of that functionality can you reproduce for $250? I am not denigrating your efforts. I am only pointing out that QB is a real bargain. And a non expert would have trouble producing much more than the objects to support a trial balance for that amount of money even at minimum wage. So if the objective is functionality in a hurry, purchasing QB is probably the wisest option. If you need integration and extensibility, then go with the ODBC driver and the multi-user versions which are considerably more expensive but still relative bargains.
 

mhakim

Member
Local time
Today, 14:27
Joined
Jan 25, 2021
Messages
66
from post owner
to all
happy to see the replies
i want to say that Quickbooks is ok no proplem but may be some day come with new technologies and Applications will showup
and it will be more effective and effecient than quickbooks
every thing has an end
there is always new beginnings
thanks all
good luck
special thanks to

arnelgp

who give the perfect answer
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:27
Joined
Feb 19, 2002
Messages
42,970
If you know what you are doing, then go for it. If you don't know what you are doing, QB is by far the safer option since all its features are audited and follow standard accounting practices. They may not be exactly what you want but it is a place to start.
 

Users who are viewing this thread

Top Bottom