I am creating a database in Access and I am confused about the order of the relationships and the table. Which table would be the junction (that requires both ID)? Also, when I update or change info in one table, I have to change it in the other tables. This should not be if the relationships are correct. The table consist of:
Students can participate in more than one program, Funders can fund more than one program, and more than one program can be present in both student and funder tables. Also, if a funder submits payment to a certain program, I would like to automatically calculate the payment in the Programs table. Example: Program needs $50,000 to operate and a funder gives $15,000. I would like the programs table to deduct and leave balance.
I have been working on this for 3 straight weeks. Any help would be greatly appreciated. Thank you