how to get single table entry view like this (see image) for % allocation of items to categories?

[mixedup]

How could I create a single table (matrix) type input view to allocate bank transactions to categories, noting one transaction can be allocated multiple categories with an associated percentage? What approach should I use in Access to get this?

So say I have the following tables:
* Transactions
* Business Category
* Transaction-Business-Allocation (which includes)
- Transaction ID
- Business Category ID
- Percent Allocation

But in terms of the means of reviewing bank account items and allocating I really want a view like this:





The specific business category could be either selected specifically, or all included (e.g. say there is less than 10 categories).

[June7]

The structure in image is not a normalized data structure and not very practical. It allows for allocation to only 3 businesses - what if you need 5? The last record does not even add up to 100%.

This would be a structure for output reporting.

[mixedup]

understood - but I wanted something to allow timely review down all bank account items and then doing the allocation - any other ideas then? I'm not allocated personal (non-business) as I don't need too really, hence the percentages won't add up to 100%. It can be all categories... Overall trying to move what I currently do in excel to access to get access to additional functionality...it's just this allocation step I'm wondering how to make as easy/time efficient as in excel

[June7]

The allocation entries would be into a subform.

A table for the transaction 'header' info and a related table for the allocation distribution details. There would be a record for each account/business combination and the allocated amount.

[mixedup]

ok - I'll look up how to do reports with subforms thanks - is it possible to get the end form (with sub-form) to be one line per bank account item like I was asking for? (or would have to be a form that shows only one transaction at a time so to speak?)

[June7]

As stated, the one account line item with multiple business allocation fields does not appear to be normalized structure. You can do it but be aware might be issues with analyzing data. Issues can be dealt with.

It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts.