Originally Posted by
bobbito
When I imagine putting all this on a single table, given each loan (XYZ Bank) could have up to 10 tranches, that seems like a giant, unwieldy table. Am I wrong... especially given the number of tranches will change over time?
Anytime you have a one-to-many relationship, such as you have here, with Loans (the 'one') to Tranches (the 'many') the standard way to do this is to use a Main Form/Subform construct.
You'd have a 'loans' Table, the basis for the Main Form, and a 'tranches' Table, the basis for the Subform. There should be a Loan ID Field as the Primary Key for the Loans Table, and a Loan ID Field as the Foreign Key in the Tranches Table.
This Field would then be used to link the Main Form and Subform. As you move from Record-to-Record in the Main Form, the related Record(s) in the Subform would be displayed. Each time a new Tranche is needed, you simply add a New Record in the Tranches Table via the Subform.
BTW, ssanfu's advice on the order of the design process is spot on...it all starts with the data!
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007