Acctg DB: First Things First

To refresh a DB man about accounting here is a page created by Erin Lawlor.

I really wouldn't want to discuss how the accounting books is generated traditionally and browsing for one, I found this page simple and yet comprehensive enough for our purposes. So many thanks and credit to Erin. Do peruse said pages if ever you want an accounting refresher.

We will try to use the journal entries from the download-able file shown in the said page: workbook.pdf file, starting at page 11.

Entry No. 1:


Using a DB system to store such entry, I would suggest the following:

I). I suggest that the second (and succeeding) line entry will have the added details as included in the first line entry which are the date and reference. But for compliance (to accountants' convention) and readability it can be made invisible by making the added details' font color white or using the cell format code: ";;;"

II). I also suggest that line entries have a copy of the description via another field which we may call "Particulars" and such is made also be invisible on the interface except on the first line.

III). Adding a field named SeqID (for sequence ID) so that when such line entries are gathered in a central database, the original entry sequence can be recreated.

IV). Adding another field DrCr which will be positive if the line entry is a Debit and negative if it is a Credit. Such way we may pivot the data container using the DrCr amount.


-----------------
If you are unfamiliar with "Data Pivot" you may see some examples here (opens in new window):

- by :onewayslim
- by : 60secbusinessinsider
- from : Mr. Excel

or see this

-----------------


Entry No. 1-16:

Following the suggestions I to IV above, I have gathered all the entries in one database container and here it is.


If you select contiguous rows of line entries, you will notice that reference, date and particulars are stored on each line as seen in this screen capture image:


Sorting or pivoting the data therefore will not result to a lost of trail.

Here is a pivot of the aforementioned source data:




. . . to be continued

---------------------------------
Procedures to follow suggestions I:

a) manual cell formatting:


b)You can automate the application of formatting invisible by following this:


Your code may be like this:

Sub Macro1()
'
' Macro10 Macro
' Macro recorded 7/15/2010 by ****
'

'
Selection.NumberFormat = ";;;"
End Sub

Now you can call such macro by following this (opens new window). Or by placing a "Forms" button or image on the interface like this.


ciao