Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-20-2010, 05:25 AM   #1
Someone123
Registered User
 
Join Date: Nov 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Someone123 is on a distinguished road
double ended bookeeping query mind bender

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.

Someone123 is offline   Reply With Quote
Old 04-20-2010, 08:07 AM   #2
Simon_MT
Newly Registered User
 
Join Date: Feb 2007
Location: United Kingdom
Posts: 2,177
Thanks: 2
Thanked 162 Times in 158 Posts
Simon_MT has a spectacular aura about Simon_MT has a spectacular aura about
Re: double ended bookeeping query mind bender

One way would be to identify by transaction types or source for each accounting function i.e.:

S Sales
P Purchases
E Payroll

Simon
Simon_MT is offline   Reply With Quote
Old 04-20-2010, 09:53 AM   #3
khawar
AWF VIP
 
khawar's Avatar
 
Join Date: Oct 2006
Location: Islamabad, Pakistan
Posts: 870
Thanks: 0
Thanked 51 Times in 16 Posts
khawar will become famous soon enough khawar will become famous soon enough
Re: double ended bookeeping query mind bender

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

__________________
Khawar
khawar is offline   Reply With Quote
Old 04-20-2010, 12:52 PM   #4
Someone123
Registered User
 
Join Date: Nov 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Someone123 is on a distinguished road
Re: double ended bookeeping query mind bender

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.
Someone123 is offline   Reply With Quote
Old 04-20-2010, 01:03 PM   #5
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: double ended bookeeping query mind bender

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 04-22-2010, 12:56 AM   #6
Someone123
Registered User
 
Join Date: Nov 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Someone123 is on a distinguished road
Re: double ended bookeeping query mind bender

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.
Someone123 is offline   Reply With Quote
Old 04-23-2010, 08:06 AM   #7
highandwild
Newly Registered User
 
Join Date: Oct 2009
Location: Jurassic Coast, England
Posts: 435
Thanks: 4
Thanked 13 Times in 13 Posts
highandwild is on a distinguished road
Re: double ended bookeeping query mind bender

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.

highandwild is offline   Reply With Quote
Old 04-23-2010, 02:22 PM   #8
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: double ended bookeeping query mind bender

Quote:
Originally Posted by Someone123 View Post
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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 12-22-2016, 11:11 AM   #9
robert_mann
Newly Registered User
 
Join Date: Dec 2016
Posts: 18
Thanks: 2
Thanked 1 Time in 1 Post
robert_mann is on a distinguished road
Re: double ended bookeeping query mind bender

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 pbaldy; 04-18-2017 at 06:49 AM. Reason: Link removed
robert_mann is offline   Reply With Quote
Old 12-22-2016, 05:12 PM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,440
Thanks: 40
Thanked 3,369 Times in 3,264 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: double ended bookeeping query mind bender

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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query cannot display available recordset colai Queries 0 01-11-2010 09:04 AM
Double Booking Query ebbluminous Queries 2 11-22-2009 10:52 AM
Double Click Listbox Record to Run Update Query on selected Record mikerea90 Forms 16 11-19-2009 03:13 PM
An interesting experiment! Need others opinion on different ODBC query behaviors. Banana Queries 12 07-01-2008 08:48 PM
Use query generated field as criteria for another query? gusdata Queries 1 04-02-2008 10:45 AM




All times are GMT -8. The time now is 08:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Is Political Correctness Toxic?

Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World