Getting category value from sub category
I'm trying to make a database that manage my bank account transactions.
In one bank account I have 2 sub categories if I can call it like that. They would be "Current Account" and "Credit Card". I separate this two categories in order that I know if the transactions were made from a regular bank account transfer or if I payed something with my card and keep track at the end of the month.
I made three tables:
Table Accounts: Account Number; Account Name; Bank Name
Table SubAccount: Account Number; SubAccount Number (credit card number); SubAccount Name
Table Transctions: Account Number; SubAccount Number;Description;Debit; Credit; Total
As you can see the relationship between the three table is made trough the Account Number and at the end I shoud be able to get the transactions related to each bank account and if I used the credit card or made a transfer by the "Current Account".
To make things easier for the user I thought that it would be nice for them to just select the "SubAccount Number" and it would automatically return the respective "Account Number" in Transaction layout.
I already tried using the Lookup function and Script Triggering but I can't get it to work.
Any ideas of how it could work? Maybe I'm getting it complicated.
Thank you very much for the help.