How to Restructure Database for New Requirements

[BikeToWork]

There is a table called tblFundBalance, with a Currency field called ExpendedFunds, where users have been entering spent funds through a user form. Now I am being tasked with creating different categories of funds for different contracts that the money comes from. This will also impact a number of front end objects such as entry forms and reports. I will still need the total expended funds from all contracts to go into the ExpendedFunds field in tblFundBalance, but will also need a way to enter individual funds for the different contracts,summing them up and putting the total in
[tblFundBalance].[ExpendedFunds]. One idea is to create a Contract table with a one to many relationship to tblFundBalance. This table would have four fields, ID (autonumber), RTID (FK to tblFundBalance), ContractName (Text) and Spent (currency). Another possible option is to create new fields in tblFundbalance, but that would not be very normalized. For now there will only be two contracts but there will be more in the future. Any advice on this is welcome.

[NeoPa]

It seems to me the only way to store this in a normalised way is to have a table that stores funds expended for each category and/or contract. Total expended funds could only ever be worked out as a sum of the relevant records.
Storing that calculable data again anywhere else is not a normalised design.

[BikeToWork]

I don't see any way to do this in a normalized fashion. The data entry form for entering Expended Amounts in Fund Balances is a continuous subform, so there is no way to put another subform inside it. Also, the Expended Amount needs to be the sum of the categories of fund sources, so that is using a field for a calculated value.

[NeoPa]

The data entry form for entering Expended Amounts in Fund Balances is a continuous subform, so there is no way to put another subform inside it.

Even if that is true, it only means it won't work with that design. In those circumstances it seems clear that isn't an appropriate design. There are many ways to crack an egg. Subforms isn't the only option available to you.

Also, the Expended Amount needs to be the sum of the categories of fund sources, so that is using a field for a calculated value.

I keep reading this but I haven't managed to work out what your point is :-(

[zmbd]

Without your business model, it's hard to help you out.

However, as a stab in the dark:

tbl_customer - obvious
tbl_accounts - maybe not as obvious; however, should be that hard to figure out.
tbl_investments - names of funds, stocks, commodities, etc... available
tbl_investmentclass - help to classify the investments as stocks, bonds, commodities, mutual funds, etc...
tbl_ledger - this is where the tbl_accounts, tbl_investments come together along with the date and the amounts.

Now this way, no matter how many different funds or types of investments you need to add, these all go into tbl_investments and classified via tbl_investmentclass. The investments should just show up in your combobox/listbox if you use the tbl_investments as the rowsource for the control.

Queries to pull information by account, crosstabs to help you group when needed, etc...