Are you authorized to change the database structure? If so, you're going to be a LOT happier in the long run.
Without knowing your current structure, I'll take a guess that it's got InvestorID, InvestorName, InvestorOtherFieldsAsNeeded, GroupID, Fund1, Fund2... Fund14?
What you really want is about five main tables:
- tableInvestors: This is for 'people'. Store InvestorID is your Primary Key (autonumber is best, but if you already have an unique internal tracking number you can use that too), InvestorFirstName, InvestorLastName, etc.
- tableGroups: This is for 'groups' (companies, plans, whatever). GroupID is your PK, any company address fields, categorization/tax-exemption, etc go here.
- tableRelationships: This table should only need three fields: RelationshipID (autonumber PK), GroupID (Long Integer), and InvestorID (Long Integer). You can also make your PK multifield (GroupID and InvestorID together), which will automatically prevent you from duplicating connections. That may or may not be appropriate for your data. What this table is doing is allowing your investors and your groups to have a Many-to-Many relationship.
- tableFunds: Like Groups, this is a 'lookup' table. It contains the FundID Primary key, of course, anything like FundName, FundTickerCode, etc that isn't dependent on the investor doing something in the Fund! This table should be fairly static, since I think you said you had 14 funds.
- tableInvestments: This is another Many-to-Many table, but this is where the meat and potatoes of your investment tracking goes. InvestmentID is your Primary Key, and absolutely needs to be autonumber here, because you're going to have a lot of entries. FundID (Long Integer) and InvestorID (Long Integer) are exactly what you will expect; you'll also probably need InvestmentDate, InvestmentAmount (Currency, probably), and any other details you track on individual deposits/cashouts. You may need a boolean field for dividend reinvestments, tax penalties, whatever... you know your business model better than I do.
Finally, you need to make sure all of your tables are linked correctly in Tools>Relationships.
- tableRelationships's InvestorID is the Many (∞) side of a One-to-Many relationship with tableInvestors' InvestorID (1).
- tableRelationships's GroupID is the Many (∞) side of a One-to-Many relationship with tableGroups' GroupID (1).
- tableInvestments's InvestorID is also the Many (∞) side of a One-to-Many relationship with tableInvestors' InvestorID (1).
- tableInvestments's FundID is the Many (∞) side of a One-to-Many relationship with tableFunds' FundID (1).
When you're done with all of that, all you'll have to do to get the data you need is open a query on tableInvestments. Group By GroupID, Sum InvestmentAmount, and put a Where restriction on InvestmentDate (you can do this automagically by quarter, or prompt the user from a form to run the query). I may be a little unclear on the nature of your 'grouping' ID, so adapt as needed.
This may seem like a lot of changes for a simple query; it's only 14 fields, right? Now wait until a year from now, when your firm has discarded 3 of those funds, added 4 others, and also wants a report based on individual investors, as well as by group. Changing it with a normalized database is going to be orders of magnitude simpler!
And yes, with a normalized database it will be easy to show all of an Investors' transactions as a subform of the Investors form (Parent/Child field will be InvestorID; the subform will be based on a query of just tableInvestments and tableFunds).