Transfer data with a table

[Guest]

Hi, I have designed a db for home finances. Simple Spend/Recieve/Bills/Loans
etc.
I use two main tables: tblTransactionsParent and tblTransactions. The Parent
table records the AccountID, date and misc notes while the Transactions table
has the detail: spendamount, recieveamount, payee, category etc. (I use 2
tables because we may withdraw X from an ATM and spend Y on gas and Z on
food. This is totalled on a form or report as needed).
What I want to do is add a "Transfer" function. That is, move an amount from
one AccountID to another.
Could anybody suggest code/help/websites that may point me in the right
direction?

TIA, Don.

 

[Guest]

I think your tables need adjusting. At the very least you should have a
table for transactions and a table for accounts. The accounts table would
have AccountID, AccountType, AccountLocation (businessname), etc.
AcountType(s) would include checking, savings, moneymarket, whatever
additional asset accounts you hold, as well as utilities, loans, etc.

The transactions table would have TransactionID (autonumber),
TransactionDate, Transaction amount, TransactionType, AccountDebit,
AccountCredit

Then, for a transfer, you'd simply add a transaction with one account
debited and the other credited. For reports, you'd sum the transactions for
each account to get a balance, and you could track activity for a given
month, etc.

As a general rule, transactions would require at least one accountID (either
debit or credit) and would usually have two. You COULD make it more of an
accounting model and make EVERY transaction require one account on the debit
side and one on the credit, but I'm not sure that's necessary.

That's just the way, at first blush, I think I'd approach it. I don't like
the idea of transactions being split and not having an account table. My
tables may need adjusting, but I think they are probably closer to the ideal
than what you describe.

Again, just my opinion. I'm no expert.

CW

 

[Guest]

CW, thanks for your thoughts. I do have the Accounts table in place and I
think I know where you're going with this. At the moment I have a tabbed form
for transactions. First tab is Spending, second tab is Deposits. I need now a
third for Transfers BUT with From and To AccountID selection. I think i can
get this going. Will let you know. Thanks again.