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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Danny - 29 Dec 2004 20:09 GMT

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

Reply to this Message

Kevin - 30 Dec 2004 10:51 GMT

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

> I have three tables:
>
[quoted text clipped - 21 lines]
>
> Danny

Reply to this Message

Danny - 30 Dec 2004 16:03 GMT

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

> Danny,
>
[quoted text clipped - 35 lines]
> >
> > Danny

Reply to this Message

Duane Hookom - 30 Dec 2004 16:26 GMT

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.

Signature

Duane Hookom
MS Access MVP
--

>I have three tables:
>
[quoted text clipped - 25 lines]
>
> Danny

Reply to this Message

Danny - 30 Dec 2004 17:05 GMT

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

> 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
[quoted text clipped - 30 lines]
> >
> > Danny

Reply to this Message

Duane Hookom - 30 Dec 2004 18:06 GMT

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?

Signature

Duane Hookom
MS Access MVP

> Hi Duane.
>
[quoted text clipped - 66 lines]
>> >
>> > Danny

Reply to this Message

Danny - 30 Dec 2004 18:41 GMT

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

> 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.
[quoted text clipped - 71 lines]
> >> >
> >> > Danny

Reply to this Message