Creating a new record via a portal to a different table
I have created a database for our foundation to keep track of single and multiple donations and pledges given by our patrons. It now has three tables: 1) DONORS, which holds all the donner contact information including the two match fields "Firstname" and "Lastname." 2) DONATIONS, which holds the donations given with a "Date" field and and "Amount" field as well as the two match fields for the Donors table. 3) PLEDGES which holds pledge payments received with a "Date" field and and "Amount" field as well as the two match fields for the Donors table.
I linked the DONORS and DONATIONS tables with the matchs fields and opened a portal in the DONORS table that lists all the dates and donation amounts brought in from the DONATIONS table. I have it set up so you can enter the date and donation amount right in the portal on the DONOR table and it creates the corresponding record in the DONATIONS table.
I have also linked the DONORS and PLEDGES tables with the matchs fields and opened another portal in the DONORS table that lists all the dates and pledge amounts brought in from the PLEDGES table. I have it set up so you can enter the date and pledge amount right in the portal on the DONOR table and it creates the corresponding record in the PLEDGES table. So far so good. But here's my problem.
I need to have a separate table for the pledges so I can perform calculations on them seperately from the outright donations (such as subtracting the running total of a client's pledges received from the actual amount he pledged, etc.). However, once a pledge payment is received and filled into the portal of his pledges, it now becomes an actual donation as well and I would like to have it automatically added as a record in the DONATIONS table.
I have been trying to make it so that once you enter the date and amount of a pledge payment into the pledge portal, it not only creates a record of it in the PLEDGES table, BUT ALSO creates one in the DONATIONS table. This should then also make it appear in the donations portal on the client's record. No matter what relationship connections I have tried, including using mulitple table occurances, I cannot seem to make this happen. I have named the date and amount fields the same in both the DONATIONS and PLEDGES tables so they would match. Hope I have been clear in what I am trying to do.
Thanks in advance for any help with this. Milo