Can I pull data from various tables into new table?
I am trying to set up the following:
- Bank transactions (separate table for each account). Although all these tables contain the same fields (e.g. transaction date, amount, item), I don't want to combine them into a single table, since then it would be difficult to have a column showing the balance after each transaction.
- I also want a separate table showing expenditure per month (one record per month). For each record, I want to pull all the relevant transactions from the corresponding month. So, for example, for the record for Mar 2013, I want to pull any transactions that took place in Mar from each and every bank account transactions table.
So far I have managed to do this by using a separate portal for each bank account, but this isn't really what I want, as I want all the March transactions to be combined (and sortable), regardless of which table they were pulled from. Is there any way to do this? I've seen reference to the "David Graham" method of adding a mid-way table, but I can't find any details of how to do this or if there are any better ways to achieve this.