self-referencing table with bridge (3 Viewers)

[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 general accounting principles. Regardless, it's still self-referencing tables with a bridge. Actually, Quicken shows them as categories along with groceries and utilities.