double ended bookeeping query mind bender (1 Viewer)

Someone123

New member
Local time
Today, 01:22
Joined
Nov 28, 2007
Messages
6
Good afternoon,

I have a simple database that contains double entry book-keeping records,

e.g.

Getting paid £50.00 (moving £50.00 from account "wages" to account "cash" would be:

ID | date | account | sum
--------------------------------------------------
0001 | 20-04-10 | wages | -50
0002 | 20-04-10 | cash | 50


The concept of double-ended book-keeping in this manner is very sound and has alot of virtues, however my problems arise when I want to view all records that went from account "wages" to account "cash".

Searching entrys for account of either would return all records with that accounts name, but not specifically those that went to a particular account.

I have another table named "journal" that has a common description for one "transaction" that corresponds to two opposite entries, like the two above.

If it were two entys in two different tables that are related to each other by a third entry in a third table, the query would be easy. But the two records that are related by a record in another table exist in the same table. How might I be able to link them, so that my query can do what I require?

Thanks in anticipation of a repsonse.
 

Simon_MT

Registered User.
Local time
Today, 08:22
Joined
Feb 26, 2007
Messages
2,177
One way would be to identify by transaction types or source for each accounting function i.e.:

S Sales
P Purchases
E Payroll

Simon
 

khawar

AWF VIP
Local time
Today, 12:22
Joined
Oct 28, 2006
Messages
870
You have to creat an extra field named voucher Number like you use in double entry accounting so your table will look like this

Code:
ID    |Voucher_Number   | date     | account | sum
--------------------------------------------------
0001  |0001             | 20-04-10 | wages   | -50
0002  |0001             | 20-04-10 | cash    | 50

In that way you can identify which debits and credits belong to single transaction by using voucher number and in the next step you can identify the wages that are paid in cash
 

Someone123

New member
Local time
Today, 01:22
Joined
Nov 28, 2007
Messages
6
Thanks for the replies Khawar and Simon_MT.

It turns out that I already have a voucher_number field, only it's called Journal_ID and it links to the journal table that I mentioned!

I would apologise for wasting your time, but I would not have realised that I had this facility as I inserted the journal table purely to avoid duplication of a description.

Thanks again. I appreciate the response and I think that this site is an very valuable resource, thanks to people like you.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:22
Joined
Sep 12, 2006
Messages
15,613
out of interest, (and payroll is a good example) - there are generally two options when posting to a NL, especially in large organisations

1. post every transaction, for whatever value
2. accumulate transactions and post totals

so with payroll, you will often total the payroll batch, and post batch totals to the NL account

in UK - gross pay summary by department, PAYE deduction, NI deduction, NI employers contribution etc etc.

So sometimes, if you want to see the full detail of the payroll, you cant get it directly form the NL, and you have to go to supporting records. Its easier to use the summarised NL, but it doesnt hold all the detail.

So if you take this to its logical extreme, you wouldnt ACTUALLY store NL postings at all - you would generate the NL from properly normalised books of prime entry.

Now I dont know whether any systems actually do this, and I am sure we've discussed all this before, but its an interesting thing to consider from a DBS point of view.

-------------
and on a slightly different topic - you may need to be careful if you are just storing a simple voucher reference. It is possible to get a posting that has no unique reference - eg 2 postings in the same Jnl batch to the same NL account. With a NL it isnt necessarily a error, but it can mean you dont have a unique key in the NL transactions tables - which may or may not matter.
 

Someone123

New member
Local time
Today, 01:22
Joined
Nov 28, 2007
Messages
6
gemma-the-husky,

Thanks for the reply. I would like to understand more of what you posted, but I don't know what an NL is?

My postings tables (transactions) has a foreign key field that references the primary key of a journal record (cotains a common description for both the credit and the debit transactions for a 'real' transaction.

In order to use a where clause on the same field from the same table twice (to select say, all transactions from "cash" that only go to "wages"), I think I would need to use VBA, rather than a single SQL query, right?

By the way, I'm not writing a payroll application. The database will be used to manage my investments and a double ended book-keeping database will be the foundation.

Thanks.
 

highandwild

Registered User.
Local time
Today, 08:22
Joined
Oct 30, 2009
Messages
435
I'm just doing something similar and I have a reference to the ID of the corresponding double entry record in each record so that I can link the two. It works well and saves storing another data item.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:22
Joined
Sep 12, 2006
Messages
15,613
gemma-the-husky,

Thanks for the reply. I would like to understand more of what you posted, but I don't know what an NL is?

My postings tables (transactions) has a foreign key field that references the primary key of a journal record (cotains a common description for both the credit and the debit transactions for a 'real' transaction.

In order to use a where clause on the same field from the same table twice (to select say, all transactions from "cash" that only go to "wages"), I think I would need to use VBA, rather than a single SQL query, right?

By the way, I'm not writing a payroll application. The database will be used to manage my investments and a double ended book-keeping database will be the foundation.

Thanks.

I thought it was an accounting app.

NL is Nominal Ledger (often called General Ledger). Basically the main accounting record, which summarises all transactions, and provides the source for all financial statements (ie p&L acounts)

I can't see why you would want double entry to manage investments, to be honest. Double-entry is only really applicable to closed system (self-contained maybe), if you like
 

robert_mann

Registered User.
Local time
Today, 01:22
Joined
Dec 7, 2016
Messages
19
Yes if you can and add an extra field name voucher Number then it will be easy to solve the equation. The double entry system of [Link removed] is based on the fact that every transaction has two parts, which therefore affects two ledger accounts. This serves as a kind of error-detection system: if, at any point, the sum of debits does not equal the corresponding sum of credits, then an error has occurred.
 
Last edited by a moderator:

CJ_London

Super Moderator
Staff member
Local time
Today, 08:22
Joined
Feb 19, 2013
Messages
16,552
The only thing to be careful of is when a transaction consists of two records. If there is a glitch the second record may not be created at which point your ledger goes out of balance. One solution is to use begintrans and committrans - if committrans is not executed, neither record is created.
 

Users who are viewing this thread

Top Bottom