Transaction table layout help.

[sdp_tws]

I am setting up a simple personal finance database and wondered if anybody could clear up which is the best way to do the following.

I have a transaction table with the following fields:

TxnID (PK)
TxnType (FK)
TxnPayee (FK)
TxnPayeeRef (txt)
TxnAccountID (FK)
TxnDate (date)
TxnRef (txt)
TxnAmount (decimal)
TxnMemo (memo)

The field TxnAccountID links to the bank accounts table - this is fine with the majority of transactions but the question I have is, how would I handle transfers between accounts?

My initial thoughts are to have to entries, one for each account (obv, one with negative, on with positive amount) but I'm not sure this is ideal. Would it be better to have to account fields (to and from)? This will cause problems at data entry.

So I'm stumped, anybody?

Thanks.

 

[HiTechCoach]

I am setting up a simple personal finance database and wondered if anybody could clear up which is the best way to do the following.

I have a transaction table with the following fields:

TxnID (PK)
TxnType (FK)
TxnPayee (FK)
TxnPayeeRef (txt)
TxnAccountID (FK)
TxnDate (date)
TxnRef (txt)
TxnAmount (decimal)
TxnMemo (memo)

I would highly recommend using the currency data type, not decimal.

TxnAmount (currency) - not decimal

The field TxnAccountID links to the bank accounts table - this is fine with the majority of transactions but the question I have is, how would I handle transfers between accounts?

My initial thoughts are to have to entries, one for each account (obv, one with negative, on with positive amount) but I'm not sure this is ideal.

IMHO, it is the ideal way and the best/only way.

Would it be better to have to account fields (to and from)? This will cause problems at data entry.

Definitely NOT the way to handle this. It would cause a lot more headaches in other places. The data entry would really not be that effected.

 

[sdp_tws]

Thankyou, I'll go for the double entry then, but how would I go about this in practice? I'd need a form that would enter two rows of data. How do you do this?

 

[HiTechCoach]

Thankyou, I'll go for the double entry then, but how would I go about this in practice? I'd need a form that would enter two rows of data. How do you do this?

My preferred method for the apps I have created is to use a Wizard form. Based on the transaction type, I will have different pages appear. When the wizard has completed gather all the required info, I use VBA code to create the required number of records