self-referencing table with bridge

[MarkK]

There should not be any negative GL postings.

I think this is true from a purist perspective, but it's convenient to just sum a single field as opposed to summing credits, summing debits, and then summing the sums.

But it is proper accounting to have debit and credit accounts, and to post debits or credits independently.

 

[RonPaii]

I think this is true from a purist perspective, but it's convenient to just sum a single field as opposed to summing credits, summing debits, and then summing the sums.

But it is proper accounting to have debit and credit accounts, and to post debits or credits independently.

Have you ever known an accountant that wasn't a purist?

 

[LarryE]

It was my understanding that the OP wished to use ACCESS to post entries to an accounting system that is as close to Generally Accepted Accounting Principles (GAAP) as possible. If you do that, then you don't use negative values in entries, and you always use offsetting debit and credit entries. The balancing of these entries should certainly occur on the entry form using the forms Before Update event where a message should alert the user that debits and credits are not balanced. I worked as an auditor for most of my working life and I can tell you that sometimes mistakes are made. If you are going to create normal Balance Sheets, Income Statements and all the other normal accounting reports, then you will need to implement something like I recommended with Asset, Liability and Equity accounts. But I was coming at it strictly from GAAP. Having said that, you'll notice I did not include any tables that track customer sales or vendor purchases orders or inventory. Those issues are handled separately but could certainly be part of the model.

 

[LarryE]

Have you ever known an accountant that wasn't a purist?

Nope.

 

[GK in the UK]

I'm not sure if you mean don't enter any negative values or don't save any negative values. I see several examples of tables with a dr and cr column. What's the point of that?

I agree that users should normally enter positive values. But surely it matters not one jot how the computer system that they're working with operates in the background.

My journal transfer form has fields for Dr and Cr, but the net value is saved in the table as a single signed column. If the journal is re-opened, the signed value appears in the correct field (unsigned of course).

 

[Gasman]

My system entered positive values.
If the transaction was a payment, I made it negative, else it was a receipt which was positive.
I only had one account to work with, keeping track of a client's money donated and spent.

Worked well for me.

 

[GPGeorge]

Your structure would be way simpler if you abandon the idea that a category and an account are not the same thing. I think Quicken misleads in that regard. A 'Category' in Quicken, if you think about it as an accounting principle, is just another Account. Quicken does that so you can define the 'from' account and the 'to' account on a single line, but it effectively hides the reality. Groceries is an expense account. Bank is an asset account. Quicken treats groceries as a different type of object, but in accounting terms, its not.

So if you had tables Transaction, Post, and Account, you could get rid of tables CheckCat, Category, Transfer, Invoice, and InvoiceItems, because all of that could be modeled with typed rows in other tables, like an invoice is just a transaction that posts a debit to a revenue account and posts a balancing credit to a receivable or bank account.

SubCategories disappear too with a self-referencing account table, which enables definition of sub-accounts.

Under the covers, I'm convinced Quicken actually stores the data this way. I've completed enough transactions involving "accounts" and "categories" to be comfortable with that assumption at any rate.

 

[Gasman]

My system entered positively values.
If the transaction was a payment, I made it negative, else it was a receipt which was positive.
I only had one account to work with, keeping track of a client's money donated and spent.

Worked well for me.

Under the covers, I'm convinced Quicken actually stores the data this way. I've completed enough transactions involving "accounts" and "categories" to be comfortable with that assumption at any rate.

My thoughts exactly since all of this O/P#s posts on this.