self-referencing table with bridge

[ClaraBarton]

I have a table of transactions.
When I transfer money from one account to another I need 2 transactions: debit to one account and credit to the other.
After Much Much messing I've decided I need a self-referencing table with a bridge between them
Everything I've read says I need a subform with the bridge table for the transfer. The subform is linked by TransactionID and fTransactionID.
Here is where I've repeatedly failed.
This is what I want:

This is the data in the subform (the tblMatch is an alias of tblTransactions):

This is the code to add the second record the to the junction table.

Private Sub Form_AfterUpdate()
    Dim strWhere        As String
    Dim strSql          As String
    Dim lngCount        As Long
    Dim PTID            As Long
    Dim MTID            As Long

    PTID = Me.Parent.TransactionID
    MTID = Me.fMatchID
    'criteria string for the compound key fields
    strWhere = StringFormatSQL("fTransactionID = {0} and fMatchID = {1}", PTID, MTID)
    lngCount = DCount("*", "tblTransferConnect", strWhere)

    If lngCount < 1 Then
       'Add match to junction table
        strSql = StringFormatSQL("INSERT INTO tblTransferConnect(fTransactionID, fMatchID)" & _
            " VALUES ({0}, {1});", MTID, PTID)
    SQLExecute strSql

    Else
        Exit Sub
    End If

End Sub

So far, no joy. The fMatchID remains blank. I've tried using TransactionID from tblMatch but it just repeats the ID from the Main form.
Does any of this make sense? What is the proper way to do this simple thing?

 

[RonPaii]

I would add a single transaction record getting it's primary key.
With that Key add the 2 lines to your transfer table, passing the primary key from the transaction table to be used as a foreign key.
The transaction table holds the date of the transation.
The amount column is not needed because it can be calculated as needed.

Transaction table
ID, Date

Transfer table
ID, TransID, Payment, Recipt

PS, use a sql transaction to add the 2 transfer records.

 

[ClaraBarton]

The tblTransactions has many more fields than shown and I actually need 2 complete transactions for each account. I started as you suggested and thought this seemed much more right. I just want to connect the two transactions to each other.

 

[MarkK]

I would use terminology 'transaction' and 'post.' The transaction is one, the posts are many. The transaction defines the date/time, and metadata common to the posts it contains, like payee, currency, etc... In each transaction the sum of posted amounts must equal zero. Each post represents a single debit or credit to a single account, so tPost looks like...

tPost
PostID
TransactionID
AccountID
Amount (positive or negative as the case may be)

Post is effectively a join table in a many-to-many relationship between tAccount and tTransaction. If you enforce that the sum of posted amounts in each transaction equals zero, then monies leaving any single account are guaranteed to be accurately distributed to other accounts, and the entire system is guaranteed to balance.

This is the fundamental structure of a double-entry accounting system. This is beautiful pattern because it also allows more than two posts in any transaction, like...

transaction
Feb 8, 2026
Grocery Store

posts
Mastercard: -200
Food.Groceries: 175
Pers.HealthHygiene: 25

So simple on the one hand, but capable of extremely complex redistribution of value within the system.
hth

 

[ClaraBarton]

I'm an accountant. I get that. And that's the way it works. I just can't figure how to get my form to post correctly.

 

[tvanstiphout]

I think you need two tables, one to be the parent with one record for a debit/credit transaction.
Then another child table with the two records in it: one for debit, and one for credit. The relationship is one-to-many from parent to child table.
The 3 total records would be created in a SQL Transaction (db.BeginTrans), so the operation is atomic = all or nothing.

 

[Gasman]

I have a table of transactions.
When I transfer money from one account to another I need 2 transactions: debit to one account and credit to the other.
After Much Much messing I've decided I need a self-referencing table with a bridge between them
Everything I've read says I need a subform with the bridge table for the transfer. The subform is linked by TransactionID and fTransactionID.
Here is where I've repeatedly failed.
This is what I want:
View attachment 123153
This is the data in the subform (the tblMatch is an alias of tblTransactions):
View attachment 123154
This is the code to add the second record the to the junction table.

Private Sub Form_AfterUpdate()
    Dim strWhere        As String
    Dim strSql          As String
    Dim lngCount        As Long
    Dim PTID            As Long
    Dim MTID            As Long

    PTID = Me.Parent.TransactionID
    MTID = Me.fMatchID
    'criteria string for the compound key fields
    strWhere = StringFormatSQL("fTransactionID = {0} and fMatchID = {1}", PTID, MTID)
    lngCount = DCount("*", "tblTransferConnect", strWhere)

    If lngCount < 1 Then
       'Add match to junction table
        strSql = StringFormatSQL("INSERT INTO tblTransferConnect(fTransactionID, fMatchID)" & _
            " VALUES ({0}, {1});", MTID, PTID)
    SQLExecute strSql

    Else
        Exit Sub
    End If

End Sub

So far, no joy. The fMatchID remains blank. I've tried using TransactionID from tblMatch but it just repeats the ID from the Main form.
Does any of this make sense? What is the proper way to do this simple thing?

If I read that correctly, don't you have your IDs the wrong way around?

 

[XPS35]

I need 2 transactions: debit to one account and credit to the other

Why do you need two transactions. There is just one transaction that involves two accounts. Like this:

 

[Gasman]

Trying to emulate Quicken?. That shows the transaction as two seperate transactions.
Probably only one behind the scenes, but they also have a debit and credit column.

 

[ClaraBarton]

Not emulating Quicken but generally accepted accounting principles. Regardless, it's still self-referencing tables with a bridge. Actually, Quicken shows them as categories along with groceries and utilities.

 

[Mark_]

As a line entry, normally you have amount, credit account, and debit account. Is there a reason not to go this way?

 

[ClaraBarton]

I do have all these amounts. That is the route I'm following. They are input on the form. The subform is following the same route but should tie the two together and then I use sql to add the second junction. (to tie the subform transaction to the form transaction when the opposite account is open) They are transactions in the tblTransactions. The only think I'm doing from a sql query is adding to the bridge table which needs no amounts. It is merely to tie the tblTransactions back to itself.

 

[Ken Sheridan]

Don't you just need two tables, one to model the assets ledger, the other to model the liabilities ledger? When a row is inserted into one code to insert a corresponding row into the other would be executed. I've attached a little demo to illustrate this. Data entry is via subforms in an unbound parent form, with the relevant code in the AfterInsert event procedures of each subform.

 

[GK in the UK]

What is this 'self-referencing' table for? When you say debit one account and credit the other, are you talking about a journal entry to the nominal ledger or are you talking about an expense receipt, a bill or something like that? Does your application have sales ledger and purchase ledger with customer and supplier accounts?

It seems to me you need a master/child relationship with two tables. To model your screenshot, Costco Visa - 1184 would be a single record in the master table. You don't need to record the 'Spend' of 61.71 anywhere. The two item lines, Household and Groceries, would each be a record in the child table linked to the parent record. The parent record (only) includes the Account ID of Costco. You don't want the Account ID in each child line.

To show Costco ledger, you list all child records with a join on the parent record and where the parent record has an 'Account' ID of Costco and grouped by the ID of the parent record. So for your example it will show a single line totaling 61.71.

You don't actually need to post the creditors side anywhere, so you don't need any sql or transaction workspace, this is a standard Access form/sub form, one-to-many. To get the creditors total for the nominal, you sum all rows in the child table which have a non-null Account ID. In this model, the child table need not balance to zero. The double entry is built in queries; your TB printout will balance even though the records in the table don't. The table total will be 'adrift' by the total of debtors and creditors.

Now, if you're talking about an expense item which is a 'cash purchase', not linked to a supplier account, or, maybe you're moving funds from current account to savings account, that is more problematic. You have to post both sides of the transaction. Personally I've avoided running unbound forms with user entry within an Access Transaction Workspace, simply because I read that the user may leave the transaction unfinished for some time and that can give issues. The more knowledgeable here might expand on that. I have a solution using a Dr child record and a Cr child record which works for me or you might record the Dr nominal and the Cr nominal in a single line (per #8 and #11) which eliminates the risk of a non-balancing ledger.

You've used the term 'Match' which to me sounds like allocating payments to invoices but I'm not sure if that's what you mean. To 'Allocate' or match invoices to payments does require an additional junction table. I record the value allocated in the junction table and partial allocations are permitted. It is essential that the sum of values in the allocation table balances to zero. To do this I build a supplier ledger into a temp table, allocate rows to each other in a form based on the temp table at my leisure then when the allocations are complete and balanced I press 'Save' and add rows to the junction table in a transaction workspace with no possibility of the user interrupting it.

 

[MarkK]

Don't you need three tables in a many-to-many relationship for a proper double entry system? I would expect to see tables Transaction, Post and Account, where Post carries an Amount field and foreign keys to the two other tables, and a rule enforced that post amounts in a transaction sum to zero.

Then, if you make a Visa purchase at Costco for groceries, clothing, and your membership is due, you create...
• one dated transaction with Costco as the Payee.
• one negative post to your Visa liability account for the full transaction amount
• three positive posts to expense accounts Groceries, Clothing and Memberships
• and enforce the rule that the sum of post amounts in the transaction balance to zero.

 

[GK in the UK]

Well, yes, perhaps I should have made it clear that there's a table for supplier accounts. But the transaction values are recorded in the item lines table only.

So we have the Accounts table (suppliers), Transaction table (Invoices, payments, journal entries), Item Lines table (item line values with the value recorded in a single signed column, an item line might be an invoice line or it might be the value of a cheque, or it might be the value of something migrated from one nominal account to another)

• one dated transaction with Costco as the Payee.
• one negative post to your Visa liability account for the full transaction amount
• three positive posts to expense accounts Groceries, Clothing and Memberships
• and enforce the rule that the sum of post amounts in the transaction balance to zero.

We don't need the post to the Visa liability account for the full transaction amount, and we don't need to enforce that the sum of posts in the transaction table balance to zero.

We can view the ledger for the Visa liability by listing rows with matching Supplier FK and negating.
We can view the ledger for Creditors Control by listing rows which have a non-null Supplier FK and negating.

To view the TB, we union two or more queries, all scanning the transaction table.

First scan, list values grouped by Nominal FK (generally we say an expense is a debit value)
Second scan, list values with a non-null Supplier FK, and negate. This gives us Creditors Control.
Third scan, list tax values grouped by Supplier FK. This gives us tax due or receivable (We would post the cheque paid to or received from HMRC to the same nominal account)

More scans as may be necessary for specialised nominal accounts, but I'm basically saying we can get everything we need from the item lines and we create the balancing double entry from the single table.

Similarly for our Profit & Loss and Balance Sheet we build the balancing rows in queries.

There is no need for anything other than a regular parent/child table. We aren't inserting records for the 'other' side, but our reporting shows the 'other' side by building it in a query. No need for Transaction Workspaces.

However, for journal entries where the transaction is not part of sales or purchase ledger, we do need to post both sides and build some method that absolutely enforces that. The parent record will have null in the column for Supplier FK.

Updating both the Dr and Cr Nominal FK in a single row does eliminate the possibility a journal being out of balance so it's kind of attractive but how do you post (for example) 5 debit lines and a single credit line which is something an accountant posting a journal entry would do. I'm not sure how that would work.

Edit: I may have got some of the negating wrong but you get the idea

 

[ClaraBarton]

OK... close.

I have the details worked out and they have been working for a long time. I just was not happy with the way I transferred amounts. Most things go into expense accounts (categories). But paying the Costco bill out of Cash. Depositing checks to Checking out of Undeposited. Drawing cash from the ATM. I always had a transfer ID within the transaction but then I got into trouble when I needed to put some of the ATM monies into Cash and some into Checking. I had to split it down and it wasn't right but I just dealt with it. So I've been reworking this old program and there's the snag. I only want the junction table for transfers. ALL transactions are in one table.

 

[LarryE]

When I transfer money from one account to another

What does this term "transfer" mean?

 

[ClaraBarton]

Transfers neither spend or accrue monies they just go from account to account.
Cash to Checking. Checking to Charge Card. Asset to Asset. Asset to Liabiility.
Paying out or bringing in (non-transfers) is receiving or disbursing monies.
Buying Groceries. Receiving a paycheck. paying the utilities. Buying inventory.
But all movement is a transaction.

 

[Ken Sheridan]

but then I got into trouble when I needed to put some of the ATM monies into Cash and some into Checking.

To accommodate the above I don't see the need for a 'junction' table. Surely the relationship type between the two instances of the tblTransacions is one-to-many. The amount taken out at the ATM would be a Debit in the referenced row, while the amounts distributed amongst the other accounts would be Credits in each of the referencing rows. The transfer of an aggregation of multiple transactions would be the reverse of this, i.e a set of Debits in multiple rows and a single Credit in one row.

The above should work, though it would not be how I'd model it. I'd have a table 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.