Creating a new record in a table related to 2nd table via 3rd tabl

[Danny]

I have three tables:

tPayments: PK is PaymentID, which is autoincrement
tDonations: PK is DonationID, which is autoincrement
tDonationsToPayments: PK is PaymentID + DonationID

A donation record will have one or more related payment records
A payment record will have zero or one related donation record

I have a main form based on tDonations, with a subform based on a query
pulling from tDonationsToPayments and tPayments

The donation record is created first, then the payment record(s). But in
order for the tDonationsToPayments table to be populated, both the DonationID
and the PaymentID have to already exist. The DonationID does already exist,
but the PaymentID does not, since the payment record hasn't been created yet.

Do I need to change the design of the query that underlies the subform? Do I
need to change the design of the main form? Do I need to run some VBA
procedure that first creates the payment record, then fills in the
tDonationsToPayments table?

Thanks for your help.

Danny

 

[Kevin]

Danny,

What if you changed the design of your tDonations table and added a foreign
key (PaymentID). This would be added when payment is made. If there are no
payment records nothing would be displayed in your subform, but when they
appear, records would populate the subform as you desire. I am not sure what
purpose the tDonationsToPayments table serves other than to tie these records
together and modifying the database design as described would achieve that
for you.

Hope that helps!

Kevin

 

[Danny]

Kevin,

Your suggestion won't work, since there may be multiple related payment
records for a donation record.

A simpler solution is to make a foreign key in the tPayments table, called
DonationID. Except in this scenario, there would be many Payment records with
no DonationID.

The reason I created the tDonationsToPayments was to allow for multiple
payments related to one donation, but without any empty fields.

Does this make sense?

Thanks for the reply - any further help is appreciated.

Danny

 

[Duane Hookom]

If you can have a payment without a related donation record then I would no
have a compound pk in tDonationsToPayments that includes the DonationID. It
seems to me the donation is optional and should not necessarily be related
to tDonations.

 

[Danny]

Hi Duane.

You're right - I have in fact changed the tDonationsToPayments table so that
the PaymentID is the primary key.

But aren't I still left with my "problem"? First, I created this "junction"
table to avoid the situation where the vast majority of Payment records will
have Null values in the DonationID field - since many payment records are not
donation payments, but for other things. So I have this junction table where
records are only created for donation payments.

My problem, given this table design, is that the Donation record and Payment
record both need to be created before the junction table can be filled in -
but it's the junction table that is the record source of the subform!

Should I just live with the empty DonationID fields in the tPayments table,
and get on with my life??

Thanks.

Danny

 

[Duane Hookom]

I would live with the empty field. You can still have a subform on your
Donation form that shows (and allows adding) payments made for the donation.
Is it possible for one check/payment to arrive that is applied to multiple
Donations/pledges?

 

[Danny]

No, a check will not be applied to multiple donations. Donations-to-Payments
is a one-to-many relationship.