Compiled General Journal in a table...

[RIAH]

Hello Friends,

I have 5 tables, in MS ACCESS including sale invoice table, purchase invoice table, cash & bank receipt table, cash & bank payment table and general voucher table.

I want to compile all the tables into one table named 'general journal' with general entries. like if data is fetched from 'sale invoice' so the automatically entry should be generated i.e.

Dr. Receivable (Customer) XXX
Cr. Sales XXX

 

[HiTechCoach]

What you want to do is possible. I have been doing it with Acess since v2.0 (1994).

I handle it with VBA code to post/un post transactions from the different journals into the general journal ledger.

A basic GL transaction ledger will need the following fields

GL Transaction ID (used to link back to the source  Transaction record)
GL Account ID (from the chart of Accounts)
GL Transaction Date
GL Debit Amount
GL Credit Amount
GL Source

 

[vba_php]

of course you can do this with queries if the data in all the tables has the same number of fields, same data types in those fields, etc....

but it would take some code to do this if it's more complex than that. but the question is, if you *are* an accountant, WHY are you doing it? in a situation like this, you are defeating the purpose of a database structure/program by putting all data in one table. while accountants *do* keep general journal logs, which makes sense, I would assume you would be better off relating your data because a database is supposed to do that.

 

[GK in the UK]

I think having all 'line' transactions in one table is the right way to do it. All linked to a 'documents' table.

I would not have separate columns for debit and credit but a single signed column for the transaction value.

I chose a schema where I write (number of item lines) + 2 to the transaction lines table. So an invoice with 1 single item line looks like this:

GL widgets sold....................+100.00 (sales income in the Profit and Loss)
TAX due on the invoice.......+ 20.00 (output tax due in the Balance Sheet)
Debtors control........................120.00 (Accounts receivable in the Balance Sheet)

Any time you sum the transaction lines table it should return zero and that's a good verification.

You will have to find a robust way to make sure that your database completes the two control account transactions without risk of failure.

I considered long and hard whether to write three gl account ID's to a single transaction line, effectively meaning the GL could never be out of balance. But it means that the control accounts would show entries for *every* invoice item line, instead of an entry for each invoice (which would correspond with usual book keeping principles)

 

[MajP]

You will have to find a robust way to make sure that your database completes the two control account transactions without risk of failure

How to use transactions in Microsoft Access VBA

How to use transactions in Microsoft Access VBA when executing multiple queries with VBA code.

codekabinett.com

Here is a real good article on doing transactions in Access. There are definitely some limitations compared to other dbs.

 

[HiTechCoach]

You will have to find a robust way to make sure that your database completes the two control account transactions without risk of failure.

It is simple if you follow the GAAP standard that uses Dedits = Credits. It has been proven to be the best method for over 2000 years.

I considered long and hard whether to write three gl account ID's to a single transaction line, ...

While you might get away with that in Excel, it causes many issues in a Relational Database.

I urge you to do some research on database normalization rules.

 

[GK in the UK]

It is simple if you follow the GAAP standard that uses Dedits = Credits. It has been proven to be the best method for over 2000 years.

Yeah but ... I wasn't talking about accounting in the line you quoted ... I know the double entry is simple and I think the OP does as well. S/he wanted an automatically generated 'double entry' and it's up to the OP to code it, and make it robust so the ledgers can't get out of balance. That's not quite so simple.

Actually I wonder if the OP wants to re-design the database, or to maintain 5 tables but write the gl to a separate table. It can all be done with one headers table and one transactions table but maybe that's not what's wanted.