Results 1 to 5 of 5
  1. #1
    brunces is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    9

    What is the best design for a credit / debit / balance table?


    Hey, guys.

    I've been wondering what would / should be the best way to store data in a table for credit / debit / balance purposes.

    Please, consider the following scenario:

    Click image for larger version. 

Name:	scenario.png 
Views:	15 
Size:	9.0 KB 
ID:	31118

    Now, three different examples:

    Click image for larger version. 

Name:	ex1.png 
Views:	14 
Size:	8.3 KB 
ID:	31119

    Click image for larger version. 

Name:	ex2.png 
Views:	14 
Size:	8.8 KB 
ID:	31120

    Click image for larger version. 

Name:	ex3.png 
Views:	14 
Size:	8.2 KB 
ID:	31121

    As far as I remember, I've always designed my tables according to the "Storage Example 3", but is it the best way to do it?

    By the way, I never store the description text; I store the description ID only, but for a better understanding of the examples, I kept the description text.

    What is the correct / best practice / most used way to store such data? Example 1, 2 or 3? Or is there another way to do it that I'm not aware of?

    Thanks for your time, guys.

    brunces

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    How important is it for you to know that an original transaction is a credit or a debit? If you reverse a credit in example 3 it becomes minus something, such as -1400. Is that then a debit or a reversal of a credit? Or doesn't it matter?

  3. #3
    brunces is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    9
    aytee111, it doesn't really matter, actually. Why?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The answer to your question
    If you reverse a credit in example 3 it becomes minus something, such as -1400. Is that then a debit or a reversal of a credit? Or doesn't it matter?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use example 3 for the easiest solution and if you don't need to know whether a negative figure is a credit or a reversal of a debit. Otherwise example 2. Would not use example 1

    Would also think you need a date field as well

    There is another scenario based on example 2, but use negative for credits per example 3. The type column then becomes more useful to be populated with DD, CASH, CHQ etc

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Debit/Credit if then
    By Drew101 in forum Queries
    Replies: 8
    Last Post: 05-26-2017, 09:26 AM
  2. debit and credit matching criteria
    By BlakeW in forum Queries
    Replies: 5
    Last Post: 02-12-2015, 08:02 PM
  3. Calculate Debit and Credit
    By naks in forum Access
    Replies: 11
    Last Post: 05-08-2013, 02:34 PM
  4. Replies: 5
    Last Post: 01-05-2013, 04:53 PM
  5. running sum for debit-credit in subform
    By roshanzarmal in forum Queries
    Replies: 1
    Last Post: 09-29-2011, 09:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums