self-referencing table with bridge

[MarkK]

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.

 

[ClaraBarton]

Sheesh! You guys are making me rethink my whole system...

 

[MarkK]

Sheesh! You guys are making me rethink my whole system...

That's why you come here.

 

[ClaraBarton]

Slept on it. You are right and I should have known better. I've spent a year trying to make it work and I was picking up Quicken traits because I imported years worth of transactions from there. Somehow it didn't click that my personal stuff is no different from business. I'm rebuilding my chart of accounts. I'll be back!

 

[RonPaii]

The above should work, though it would not be how I'd model it. I'd have a table row for each type of account, with columns representing the shared attributes of each type of account in that table, and an AccountNumber foreign key referencing an all-encompassing Accounts table whose columns represent attributes shared by all account types. The tables are thus correctly normalized to 3NF at least, with all non-key columns being functionally determined by the key. I do this with my own personal finances database. Transfers simply require the insertion of appropriate rows into each appropriate table, one or more debits in one, and one or more credits in the other.

I think it should be row for each type of account not table.

 

[Ken Sheridan]

I think it should be row for each type of account not table.

I worded it badly. What I meant was that for all accounts with the same attributes I'd have a single transactions table with an AccountNumber foreign key. So there could be one table for CurrentAccounts ('checking' accounts in US-speak) and one for DepositAccounts, and so on. Each row in all these tables would reference the primary key of an Accounts table, whose attributes would be AccountNumber, AccountName, etc.

 

[RonPaii]

I worded it badly. What I meant was that for all accounts with the same attributes I'd have a single transactions table with an AccountNumber foreign key. So there could be one table for CurrentAccounts ('checking' accounts in US-speak) and one for DepositAccounts, and so on. Each row in all these tables would reference the primary key of an Accounts table, whose attributes would be AccountNumber, AccountName, etc.

I still don't like that structure; it encodes information about the account type in the table name. IMO have a single account table and an attribute table. One of the attributes would identify how the account is used. Only columns shared by all accounts would be in the account table. Now the posting table can use the Account table's PK as a FK for all posts. When entering transactions the account list combobox can be limited by accounts with the correct attribute.

 

[LarryE]

Sheesh! You guys are making me rethink my whole system...

It needs to be rethunk. You are attempting to create a debit/credit bookkeeping system with no debit or credit entries that we can see. Further, a transfer of funds between accounts only requires debit and credit entries. But since you don't use those, you are having problems.

 

[Ken Sheridan]

You are attempting to create a debit/credit bookkeeping system with no debit or credit entries that we can see.

I assume the sign of the value in the Amount column determines whether its a credit or debit. Not something I'd favour though.

 

[RonPaii]

I assume the sign of the value in the Amount column determines whether its a credit or debit. Not something I'd favour though.

I originally made the mistake of doing negative amounts, luckily an accountant put me straight and showed me how to do reverse postings. I always had a credit an debit column but had them in the wrong order.

 

[Ken Sheridan]

Coming back to the original topic of this thread, the following two 'append' queries are examples of how aggregated values from multiple transactions in one account can be transferred to a another account in the Transactions table, in this case from transaction numbers 1 and 2, both from account 2, into account 3, firstly to insert a row for account 3:

INSERT INTO
    Transactions (AccountID, TransactionDate, Credit, Debit)
SELECT
    3 AS TransferToAccount,
    DATE() AS TransferDate,
    IIF(SUM(Credit - Debit) > 0, SUM(Credit - Debit), 0) AS CreditTransfer,
    IIF(SUM(Credit - Debit) <= 0, SUM(Credit - Debit), 0) * -1 AS DebitTransfer
FROM
    Transactions
WHERE
    TransactionID IN (1, 2);

and to insert a corresponding row for account 2:

INSERT INTO
    Transactions (AccountID, TransactionDate, Credit, Debit)
SELECT
    2 AS TransferFromAccount,
    DATE() AS TransferDate,
    IIF(SUM(Credit - Debit) <= 0, SUM(Credit - Debit), 0) * -1 AS CreditTransfer,
    IIF(SUM(Credit - Debit) > 0, SUM(Credit - Debit), 0) AS DebitTransfer
FROM
    Transactions
WHERE
    TransactionID IN (1, 2);

The two would be executed within a Transaction. Rather than using literal values, the TransferToAccount and TransferFromAccount values would be parameters referencing unbound combo boxes of course. The IN operator does not accept parameters, but the InParam function from the following module published by Microsoft many years ago can be used instead:

' Module basInParam
' The functions in this module were published by Microsoft, but
' the article in question is no longer available.
' They are used to simulate the use of the
' IN operator in a query, but by accepting a value list as a parameter
' rather than a literal value list as the IN operator requires.

Option Compare Database
Option Explicit

Function GetToken(stLn, stDelim)
    
    Dim iDelim As Integer, stToken As String
    
    iDelim = InStr(1, stLn, stDelim)
    
    If (iDelim <> 0) Then
        stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
        stLn = Mid$(stLn, iDelim + 1)
    Else
        stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
        stLn = ""
    End If
    
    GetToken = stToken
    
End Function

'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the IN operator.
'============================================================
Function InParam(Fld, Param)

    Dim stToken As String
    'The following two lines are optional, making queries
    'case-insensitive
    Fld = UCase(Fld)
    Param = UCase(Param)
    
    If IsNull(Fld) Then Fld = ""
        Do While (Len(Param) > 0)
        stToken = GetToken(Param, ",")
        If stToken = LTrim$(RTrim$(Fld)) Then
            InParam = -1
            Exit Function
        Else
            InParam = 0
        End If
    Loop

End Function

The parameter passed into the InParam function would be a hidden text box populated by the selections in a multi select list box. This list box would be correlated with an account number combo box, so the process would be to first select the target account in one combo box, and the source account in another combo box (the one with which the list box is correlated). The multiple source transactions would then be selected in the list box, and a value list of TransactionIDs would be built by iterating through the list box's ItemsSelected collection. The two 'append' queries would then be executed to insert two rows into the Transactions table.

To distribute parts of a value from a single transaction into multiple accounts, as in the example of an ATM withdrawal you posted earlier, would require the selection of that transaction in a combo box, then multiple accounts would be selected in a multi-select list box. Code would then iterate through the list box's ItemsSelected collection, and at each iteration of the loop a row would be inserted into into the Transactions table, one for each target account. Credit and Debit values of 0 would be inserted in each case, however. The newly inserted rows would then be displayed in a form, in continuous forms or datasheet view, in which you'd insert the individual credit values into each account. An 'append' query similar to the second one above would then be executed to insert a corresponding Debit transaction into the source account.

 

[Ken Sheridan]

I still don't like that structure; it encodes information about the account type in the table name. IMO have a single account table and an attribute table. One of the attributes would identify how the account is used. Only columns shared by all accounts would be in the account table. Now the posting table can use the Account table's PK as a FK for all posts. When entering transactions the account list combobox can be limited by accounts with the correct attribute.

If I were to use a single accounts table I think I'd model the different account types as sub-types in a type hierarchy. That way every transaction, for all accounts, would have a distinct key value.

 

[ClaraBarton]

I like Mark's best... Transaction to Post (one to many), Post to Account (one to one). 2 foreign keys in Post (transaction and account).
Data Entry form: balance sheet accounts with sub form being Post amounts with combo for account.
Am I right?

 

[RonPaii]

I like Mark's best... Transaction to Post (one to many), Post to Account (one to one). 2 foreign keys in Post (transaction and account).
Data Entry form: balance sheet accounts with sub form being Post amounts with combo for account.
Am I right?

Something like the following?

tblAccount

ID	AccountName	Description

tblTransaction

ID	Description	TransDate

tblPost

ID	TransID(FK)	AccountID(FK)	Credit	Debit

 

[ClaraBarton]

Something like the following?

tblAccount

ID	AccountName	Description

tblTransaction

ID	Description	TransDate

tblPost

ID	TransID(FK)	AccountID(FK)	Credit	Debit

See... back to my junction table tblPost

 

[LarryE]

In this example, 2 transactions occurred on 2/12/2026:

1. VISA credit card was used to purchase groceries at Costco for $100.00. A debit is made to Costco expense and a credit is made to VISA credit Card (Short-Term Debt).
2. Money was withdrawn from an ATM from Bank Checking Account (a Debit Card) to Petty Cash (and the pocket of the person withdrawing it) for $50.00. A debit is made to Petty Cash and a credit is made to Bank Checking Account.

Of course, you will want to add relevant fields to these tables as you wish, but this is just a simple example of double-entry. If you are creating invoices, then an invoice table linked to a customer table and the Transactions table can be made. I didn't create an Income Sales account here, but you can create one in the TypeCategory table with an AccountTypeFK of 4 (Income). Maybe this model can give you a start.

I attached the model if you wish to use it as a guide.

 

[MarkK]

Here are my accounting system tables.

• Journal rows that are in Invoices and Bills require a linked Payee, enabling creation of a Payee statement.
• AccountTypes are Asset, Expense, Revenue, Liability, Equity.
• This is the simplest Journal form hosting the simplest Post subform...

Maybe this offers some ideas.

 

[Gasman]

I originally made the mistake of doing negative amounts, luckily an accountant put me straight and showed me how to do reverse postings. I always had a credit an debit column but had them in the wrong order.

When I created a small system when I was divisional treasurer with SSAFA, I had negative amounts for payments?
Worked fine for me?

What is the issue with negative amounts?

 

[RonPaii]

When I created a small system when I was divisional treasurer with SSAFA, I had negative amounts for payments?
Worked fine for me?

What is the issue with negative amounts?

There should not be any negative GL postings.

 

[Gasman]

Mine was a simple money in, money out on one account?