Access 2007 - Splitting single record into component parts similar to Microsoft Money

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:

Import Table
Date.......Payer/payee...CR/DR....Amount......Category
19/4/11...Amazon.........DR..........100............(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 my limited Access knowledge?

Thank you

I would create a second table called Transactions which you can link back to your bank statement. That table would have the following fields

PK -autonumber
Category - text
Description - text
GrossAmount - currency
VATAmount - currency
BSFK - long (links to statement PK)

When you are importing the bank statement, ensure the statement table you import to has an autonumber PK field which is the unique ID for that transaction.

Then:

1. create two forms based on each table - better as continous but could be datasheet view. In both cases, you can set the PK's and FK field visible property to false

2. create a new unbound form and drag the two forms onto it as subforms - statement at the top, transactions at the bottom

3. On the new unbound form, create an unbound control called MasterID, when you are happy everything is working after the following actions, you can set it's visible property to false

4. In the Statement subform, go to the subform on current event and enter the following code

Parent.MasterID=PK

5. In the Transaction subform control properties set the LinkMasterField to MasterID and the LinkChildField to BSFK.

6. In the Transaction subform go to the subform beforeupdate event and put the following code

BSFK=Parent.MasterID

That should be it.

Open the form in normal view and as you click on each statement item, the transactions will be listed below.

To enter transactions against each statement item, select the item then enter the details in the Transactions subform.

Note that further work needs to be done to provide dropdowns for categories and to ensure amounts entered total the statement but see if you can get this working before you tackle that part.

It is probably work checking if Microsoft Money provides an ODBC connection. If it does you could create linked tables so you can see the structure this package uses - for example how does it treat categories.

An account and a category are essentially the same thing. If you are writing a double entry accounting system I would have a transaction table, and a post table and an account table. Each transaction contains many posts and every post links to an account (category or bank). And all the posts in a single transaction balance to zero. Money can then get posted out of a bank account to an expense account and a VAT or GST account.

CJ_London and lagbolt

Wow! Thank you both for very kindly taking the time to respond to my request for help so quickly with your very helpful suggestions.

Grahame

I understand that the use of lookups within tables is frowned upon.

Click to expand...

Don't confuse lookups defined on table columns with combos defined on forms. The former is bad, the latter is good