If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Creating a new record in a table related to 2nd table via 3rd tabl
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 |
#2
|
|||
|
|||
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" wrote: 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 |
#3
|
|||
|
|||
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 "Kevin" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
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. -- Duane Hookom MS Access MVP -- "Danny" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote: 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. -- Duane Hookom MS Access MVP -- "Danny" wrote in message ... 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 |
#6
|
|||
|
|||
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? -- Duane Hookom MS Access MVP "Danny" wrote in message ... 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" wrote: 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. -- Duane Hookom MS Access MVP -- "Danny" wrote in message ... 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 |
#7
|
|||
|
|||
No, a check will not be applied to multiple donations. Donations-to-Payments
is a one-to-many relationship. "Duane Hookom" wrote: 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? -- Duane Hookom MS Access MVP "Danny" wrote in message ... 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" wrote: 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. -- Duane Hookom MS Access MVP -- "Danny" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
ComboBox fields to Update record in a table | LMB | New Users | 6 | September 20th, 2004 09:27 PM |
How do I copy the LAST RECORD of one table onto another TABLE? | JANUS1356 | General Discussion | 2 | September 9th, 2004 11:08 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |