Creating New Table with Automatic Relationships?

[bobbito]

Hello All,

I'm just starting to prototype this db so I don't have anything to .zip up to the site but can someone please tell me if I'm on the right track or offer a clue as to how to execute the following:

Can I create a form, and on a button click that form automatically generates a new table. I already know the fields of this table, so those fields and data types will be constant elements every time a new table is generated. The key piece of this issue, is that upon the creation of the new table, it automatically links (builds a relationship) to another table?

Background:



I have a loan, lets say XYZ Bank. This loan has "subloans" attached to it, these subloans are called a "tranche." So I may have XYZ Bank Tranche 1, and XYZ Bank Tranche 2... what if the user wants to create a third tranche? I want them to have that ability. I think its good db design to have each tranche in its own table, please tell me if I'm wrong on this.

When user adds the third tranche, they're going to input "starting details" which will include the borrowed amount in the new tranche, fees, and interest rate.

Right now I have a base table, just called "Loans" where I'm only storing the name of each loan. I'm thinking the PKey from Loans needs to be the FKey for each tranche so that the data always stays related.

Any help is much appreciated. I'm somewhat new to Access and VBA.

[pbaldy]

Good design would not have separate tables:

http://r937.com/relational.html

[bobbito]

Hi Paul

Thanks for the link, its going to take me time to digest much of it. But in my case, each tranche needs to

(1) have its own interest rate,

(2) its own interest calculation,

(3) the ability to change the rate (for example 45 days accruing at 2%, 45 days accruing at 2.5%),

(4) unique payment dates, we may pay off the loan in the middle of a period, we might not, idk, so I need to input a payment date, splitting that payment between interest and principal, and refreshing the ending principal balance and total interest accrual for a given period

(5) user defined periods. Some of our accruals go through calendar month end, others go through fiscal month end, so I want the user to go into a form, "create" a period, manually add interest and principal payments, update the interest rate if need be, and then get a report output showing the daily balance and interest accrual for that period, any manual inputs, and an ending total of principal and interest for the period and ytd.

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?

[bobbito]

But... heeding your advice, am I then really seeking to create a new column (or set of columns) every time the user wants to add a new tranche?

And how would I map a form input to a specific group of columns in a table?

For example, I want the user to be able to work on one tranche at a time. So lets say the click into "Accrual Reports" and select XYZ Bank from a combo box. They then select "Tranche 1" from a second Combo Box. They input dates into some text fields and click "Generate Period."

I guess this would trigger a "superficial" report showing daily principal balance and interest accrual. They could then, using more fields on that form input a Principal Payment, when they hit "submit" for that payment, it will appear on their report for the day the specified. The principal the next day would be reduced by the amount of the payment (they're seeing this happen live, on screen). This would also force a new interest accrual value. Then they can do the same for interest payments, and updating the rate.

But all these inputs need to be recorded in that single table, so how am I pointing a principal payment applied to Tranche 1 of XYZ Bank to be exclusively applied there, and not for some other tranche?

[bobbito]

I hope now I'm thinking like an Access developer! I really will listen to you guys, just be patient with me, plz! I made a form, I guess to take the next step I need to dive into some VBA and make cascading combo boxes that would allow the user to selectively assign activity to specific tranches.

Also, in terms of generating that "superficial" report, that's calculations I want to happen live on a form for the user to see the impact of their changes. Any clues on how I could execute that?

AH, before I forget, I am also guessing that I will use VBA to calculate all the changes for a user up to and through a specified time period, ie, in year 2021, if the user wants to see activity from Jan/2021 to Feb/2021, the VBA will perform calculations starting from today (May/2016) to Jan/2021 to give a "starting balance" then complete the calculations through Feb/2021 to show an ending balance?

[ssanfu]

I see you have already jumped ahead to creating forms without ensuring your table structures/relationships are correct.

You should design your table structures/relationships in paper (whiteboard, chalkboard, cardboard, sticky notes, etc) BEFORE you get on the computer.
Work through MANUALLY adding a couple of records to test your design (that you are doing on a whiteboard).

I would think you need at least 2 tables, possibly 3 or 4 (or 5 or 6). I just don't know enough about your process/requirements.

Maybe you would post a pic of your relationship window??

But... heeding your advice, am I then really seeking to create a new column (or set of columns) every time the user wants to add a new tranche?

Nope. You would create more RECORDS. Anything that changes your table design (adding tables, adding columns) means, to me, your dB has a design flaw.

[Missinglinq]

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)>

[bobbito]

Database2.zipThanks for your help guys. I deleted the original form, added more fields and relationships and tried to regenerate it except this time its not actually working But the whole db is attached, please let me know your input.

[ssanfu]

Shouldn't use spaces in names. Only letters and numbers - exception is the underscore.

I would think you would need a date field for the initial loan amount.

I played around with your dB...... I really hate the switchboard menu.
Took me 15 minutes (then used Google) to find the switchboard manager.


I don't know where you are headed with this project, so just use this dB as an example.......



PS.....I added one Accrual to bank "XYZ", Tranche 2. A filter could be added to the "Add Accrual" to select/display only 1 Tranche.... the sub form now displays all tranches.



Good luck with your project....