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
Funders
Programs

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

you need to add tables to act as junction tables

for instance,

StudentPrograms
StudPrgID, autonumber
StudentID, long integer
ProgramID, long integer


ProgramFunders
PrgFundID, autonumber
FunderID, long integer
ProgramID, long integer
Amount, currency
DatePaid, date

in your Programs table, you will have an Amount that you need -- you should not store what is left to pay as it can be calculated anytime

PrgBalance: Programs.Amount - nz(dSum("Amount","ProgramFunders","ProgramID=" & [ProgramID]),0)