Complex Union Query to duplicate records 2

[diwin]

Two tables are involved:

tblDonations: holds all donations.
tblDtDesignation: holds all designations (allow multiple designations) for all donations.

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.

In the Designations table:
field: dtdesignation_FdonationID - the foreign key linking to the Donations table. For grouped donations, designations are only recorded for the Main donationID. So no designations in the Designations table have a foreign key relating to a non-Main group donation.

So, 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.

I just can't wrap my brain around the SQL that would be involved. I sure hope someone out there derives pleasure out of solving this type of problem; it just makes me dizzy. Any attempt much appreciated. If I didn't explain it well, please let me know.

Daniel Dillon
o (<--- brain shown at actual size.)

 

[lynchg]

Is the GroupID for each individual in a group the DonationID of the of the "Main" group member?

 

[diwin]

Thanks lynchg.

The donations table contains, among other fields, donationID, donationAmt, donationGroupID, donationGroupAmt.

The designations table contains dtdesignationID, dtDesignation_FdonationID.

So 2 donations are shown below, one Individual and one Group donation.

Individual with 2 designations:
donationID = 5, donationAmt = $30, donationGroupID = 0, donationGroupAmt = $0

dtdesignationID = 7, dtdesignation_FdonationID = 5
dtdesignationID = 8, dtdesignation_FdonationID = 5

Group 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.



Daniel Dillon
o (<--- brain shown at actual size.)

 

[lynchg]

Try this:

Select D1.donationID, D1.groupID, DS.dtdesignationID
From tblDonations as D1, tblDonations as D2, tblDtDesignation as DS
Where D1.donationGroupID = D2.donationGroupID
And D2.donationID = DS.dtdesignation_FdonationID;

You will need to add the other fields (names, amounts, etc),
but I think that will pull the elements together like you want.

Good Luck

 

[diwin]

Thanks lynchg

I don't know that particular structure. I ran the query and it created a monster which just kept growing. I stopped at 27,000 records; there are only 3,952 donations. There are 29 possible designations.

It seemed to be associating every donation with every designation, starting at 1.

I don't know how to improve that. Any ideas???

Daniel Dillon
o (<--- brain shown at actual size.)

 

[lynchg]

There must be multiple incidents of donations by the same foundation or donor, right?

Does the DonationID and DonationGroupID stay the same for a group across multiple donation instances?

 

[diwin]

One person or organization can make any number of donations.

Here is a sample from tblDonations. I included some grouped donations and some non-grouped (GroupID = 0) donations. Below that are the associated records from tblDtDesignation.

donationID Amount GroupID GroupAmt
613 $91,000.00 1 $45,500.00
614 $0.00 1 $45,500.00
970 $60.00 2 $30.00
971 $0.00 2 $30.00
1831 $50.00 3 $10.00
1832 $0.00 3 $10.00
1833 $0.00 3 $10.00
1834 $0.00 3 $10.00
1835 $0.00 3 $10.00
3975 $450.00 0 $0.00
3976 $100.00 0 $0.00
3977 $2,000.00 0 $0.00
3978 $30.00 0 $0.00



dtDesID dtDesAmt dtDes_FdonationID
610 $91,000.00 613
965 $60.00 970
1824 $50.00 1831
3939 $450.00 3975
3940 $100.00 3976
3941 $2,000.00 3977
3942 $30.00 3978



Daniel Dillon
o (<--- brain shown at actual size.)

 

[lynchg]

Looking at the first 2 records in your tblDonation sample, GroupID = 1.

How many other sets of records in tblDonation might there be for GroupID 1? And how do you distinguish between one donation incident from Group 1 and another? Date?

If so you would need to include the date field in your link between D1 and D2 in my SQL, like:

Select D1.donationID, D1.groupID, DS.dtdesignationID
From tblDonations as D1, tblDonations as D2, tblDtDesignation as DS
Where D1.donationGroupID = D2.donationGroupID
AND D1.DateValue = D2.DateValue
And D2.donationID = DS.dtdesignation_FdonationID;

Try something like that for starters, if there are other fields that would need to be included to narrow yourself down to a specific donation incidence, they would need to be included as well.

 

[diwin]

I don't follow.

Firstly. Group donation 1 has 2 members. A donation record is created in tblDonations for each "person" in the group. But the value in tblDonations.donationAmt is set to zero, except for the first donor in the group; tblDonations.donationAmt for that donationID is the actual amount for the donation by the group.

Incidentally, maybe this will help. The donation record that gets the whole group amount in that field, and is associated with the Designation record in the tblDtDesignation is the lowest donationID for the group. i.e. If there is a five-member group, as for Group Donation ID = 3, above, the lowest donationID in the group, in that case 1831, is the donationID that appears as the foreign key in the Des record in tblDesignation, in that case dtDesID = 1824. Can I just tell my query to assign the same designation to secondary group members as applied to the group member with the numerically lowest donationID???




Daniel Dillon
o (<--- brain shown at actual size.)

 

[lynchg]

In the post above with the sample of data, I see 2 records with a groupID of 1 in tblDonation. Are there any other records in that table with groupID values of 1?

 

[diwin]

no.
Each group can have any number of records.

Daniel Dillon
o (<--- brain shown at actual size.)

 

[lynchg]

Then I don't understand why my first query didn't work.

Have you included all of the fields from both tables in your sample sets of data?

What do you do if the organization represented by GroupID 1 makes another donation? show me the tblDonation records for a 2-person donation from that same organization totaling $10,000, split $6,000 and $4,000.

There must be something I am missing one of the variations of my SQL should have given you the data you need.

 

[diwin]

See this Excel spreadsheet

I included 3 donations: one is a non-group donation, the other 2 are grouped donations. DonorID 683 was the "Main" donor in both group donations and also made the other one.



Daniel Dillon
o (<--- brain shown at actual size.)

 

[lynchg]

So every time the group led by donorID 683 makes a donation, a new groupID value is created in tblDonation that doesn't exist for any other group donation, correct?

 

[diwin]

It is simpler than that.

Groups are only formed for the purpose of the donation and have no existence after that. It is like this... Mom writes a cheque, but all the kids gave Mom a fiver toward the donation. Next week, Mom and a workmate make a donation; next day, one of them makes another donation, solo this time. Only 2 of the above 3 donations are grouped. Grouping is mainly so that all people who contributed to a donation can get a receipt made out to them, but only ONE actual donation is recorded. Sometimes 30 people at work join together to make one donation, and all 30 want a receipt.

Whenever a donation is recorded, a groupID is assigned. If only one person makes the donation, say $40, the donationAmt is $40, the GroupAmt is zero and the groupID is automatically zero and the DesignationID (can be more than one)is added to tblDesignation referencing the donationID.

If other people are involved, say 3 more, added using the input form, which holds them in a temporary table for group members, then when the donation is added to the table, the main donation record gets all the donation info, including a donationAmt of, say $40, plus the next incremental groupID available, say 17. The other 3 people in the temp table for group members also get a record added to the donations table for them, also with groupID 17, but with a donation amount of zero. Let's say they all put in $5 each. Then the GroupAmt for the 3 Group members would all hold $5 each, and the Main donor would then show a groupAmt of $25.($40 - 3 x $5 = $25). Also, there is no record added to tblDesignation for these extra 3 group donors.

The problem is that, each of the other 3 members in the group donation may want a receipt for their contribution. I can read the amount from the groupAmt field, but I can't read the designation info to put in the "Re:" line of the receipt. For that, I need to hijack the Main donor's donationID from group 17 to get access to that record's designation info.

Daniel Dillon
o (<--- brain shown at actual size.)

 

[lynchg]

I can't understand why that first SQL statement didn't work then. If a particular groupID only refers to one particular donation event and the donationID that gets entered into the dtDes_FdonationID field in the designations table only refers to that particular donation event then you shouldn't get multiplication of every donation and designation.

There is something with your data that I don't understand or that you aren't telling me that is causing the problem.

 

[PHV]

A quick way.
Create a query named qryDonationLeader:
SELECT A.donationID, A.donationAmount, A.donationGroupID, A.donationGroupAmt, B.dtDesignationID, B.dtDesignationAmount, B.dtDesignation_FdonationID
FROM tblDonation AS A INNER JOIN tblDtDesignation AS B ON A.donationID = B.dtDesignation_FdonationID;
Create a query named qryDonationGroup:
SELECT G.donationID, G.donationAmount, G.donationGroupID, G.donationGroupAmt, L.dtDesignationID, L.dtDesignationAmount, L.dtDesignation_FdonationID
FROM qryDonationLeader AS L INNER JOIN tblDonation AS G ON L.donationGroupID = G.donationGroupID
WHERE G.donationGroupID<>0;
And now your query:
SELECT * FROM qryDonationLeader
UNION
SELECT * FROM qryDonationGroup

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886