I need to create a query but I can't wrap my brain around it. Two tables are involved:
tblDonations: holds all donations.
tblDtDesignation: holds all designations (allow multiple designations) for all donations.
When I print receipts, I need a query that displays all the designations in the designation table, plus the missing ones for the group members. Their designation(s) is/are the same as the designation(s) for the Main donationID in their group.
Two donations are shown below, one Individual and one Group donation.
Individual Donation with 2 Designations:
donationID = 5, donationAmt = $30, donationGroupID = 0, donationGroupAmt = $0
dtdesignationID = 7, dtdesignation_FdonationID = 5
dtdesignationID = 8, dtdesignation_FdonationID = 5
Group Donation with 1 Designation:
donationID = 23, donationAmt = $90, donationGroupID = 17, donationGroupAmt = $30
donationID = 24, donationAmt = $0, donationGroupID = 17, donationGroupAmt = $20
donationID = 25, donationAmt = $0, donationGroupID = 17, donationGroupAmt = $40
dtdesignationID = 34, dtdesignation_FdonationID = 23
What I would like to acheive in the query is to associate designationID 34 with donations 24 and 25, as well as 23.
When individuals are grouped in one donation, each group member gets their own donation record in the tblDonations table.
field: donationAmt - set to zero for all group members except the Main donor in the group.
field: GroupID - relate group members to each other.
field: GroupAmt - the amount each contributes.
Daniel Dillon
o (<--- brain shown at actual size.)
tblDonations: holds all donations.
tblDtDesignation: holds all designations (allow multiple designations) for all donations.
When I print receipts, I need a query that displays all the designations in the designation table, plus the missing ones for the group members. Their designation(s) is/are the same as the designation(s) for the Main donationID in their group.
Two donations are shown below, one Individual and one Group donation.
Individual Donation with 2 Designations:
donationID = 5, donationAmt = $30, donationGroupID = 0, donationGroupAmt = $0
dtdesignationID = 7, dtdesignation_FdonationID = 5
dtdesignationID = 8, dtdesignation_FdonationID = 5
Group Donation with 1 Designation:
donationID = 23, donationAmt = $90, donationGroupID = 17, donationGroupAmt = $30
donationID = 24, donationAmt = $0, donationGroupID = 17, donationGroupAmt = $20
donationID = 25, donationAmt = $0, donationGroupID = 17, donationGroupAmt = $40
dtdesignationID = 34, dtdesignation_FdonationID = 23
What I would like to acheive in the query is to associate designationID 34 with donations 24 and 25, as well as 23.
When individuals are grouped in one donation, each group member gets their own donation record in the tblDonations table.
field: donationAmt - set to zero for all group members except the Main donor in the group.
field: GroupID - relate group members to each other.
field: GroupAmt - the amount each contributes.
Daniel Dillon
o (<--- brain shown at actual size.)
