I currently import a bank statement into an Access 2007 table and then use a lookup within the table to categorise each transaction. I then use a report to group the transactions into category types.
However, some of the bank account entries may need to be split further to accurately reflect the breakdown of the transaction into its appropriate categories. For example, a payment to Amazon may be made up of CDs, Books and Software.
I would like to be able to categorise each record into its component parts similar to the way that Microsoft Money splits a transaction – see the attached Microsoft Money screen grab showing my Amazon example split into its 3 component categories.
My database essentially consists of a Bank Account (Import) Table and a Categories Table (containing just a list of categories). I then go though the Bank Account (Import) Table and categorise each entry manually using a lookup:
I understand that the use of lookups within tables is frowned upon.
I should therefore appreciate if someone could steer me in the direction of how best to categorise/split such transactions using (i) tables/queries and/or (ii) programmatically.
Or, is this just a step too far for someone with limited Access knowledge?