Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Talking Unanswered: A tale of three tables.

    Okay so I need help. Here is the problem.....

    I have 3 tables. {Well actually more, but I don't think they matter right now....}
    1. Donors
    2. Donations
    3. Pledges

    Pledges do not turn into Donations but both donations and pledges are gifts.

    Both the Donations and Pledges tables have the field DonorID. Thus linking them to the Donors table. Also, both tables have the fields "Fund" "DatePaid" and "Amount".


    Each donor gives a "gift", the "gift" is designated to a particular fund. I would like to produce a form called "Gift History" that is donor specific that can be viewed as a subform on the Donor form which reflects each "gift" the donor has given, the amount of the gift, and the date. I would like it to be a tabular form with the following fields:
    "Fund" "Gift Date" "Gift Amount".

    I would like the fields to reflect all gifts that have come in- both donations & pledges. Even if the pledges aren't paid.
    I would also like the form to update with each new entry.
    Is this possible?

    P.S. To get an idea of what I'm referring to in regards
    to the layout of "Gift History", I used lay out for "Event Registration History" on the Attendees form in the Event Managment Template as a guide.

    Thanks for any help you guys can give.

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Imo, you'd be doing yourself a favor if you changed your story to "A tale of two tables":
    1. Donors (as is)
    2. PledgesAndDonations: DonorID, Fund, DatePledged, DatePaid, Amount

    Anything with NO DatePaid is a "pledge".
    Anything WITH a DatePaid is a "donation".

  3. #3
    Join Date
    Aug 2003
    Posts
    18
    Originally posted by JTRockville
    Imo, you'd be doing yourself a favor if you changed your story to "A tale of two tables":
    1. Donors (as is)
    2. PledgesAndDonations: DonorID, Fund, DatePledged, DatePaid, Amount

    Anything with NO DatePaid is a "pledge".
    Anything WITH a DatePaid is a "donation".
    I can't do it that way. Pledges have to remain separated from Donations because when a payment comes in it is entered. I wish I could take the easy way out..... I only need them to be combined when viewing a donor's gift history.

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    You can combine the data with a union query:
    Code:
    SELECT Pledges.DonerID, Pledges.Fund, Pledges.DatePaid, Pledges.Amount, "Pledge" AS Type
    FROM Pledges
    UNION ALL
    SELECT Donations.DonerID, Donations.Fund, Donations.DatePaid, Donations.Amount, "Donation" AS Type
    FROM Donations
    ORDER BY DonerID, Fund, DatePaid, Type;

  5. #5
    Join Date
    Aug 2003
    Posts
    18
    Originally posted by JTRockville
    You can combine the data with a union query:
    Code:
    SELECT Pledges.DonerID, Pledges.Fund, Pledges.DatePaid, Pledges.Amount, "Pledge" AS Type
    FROM Pledges
    UNION ALL
    SELECT Donations.DonerID, Donations.Fund, Donations.DatePaid, Donations.Amount, "Donation" AS Type
    FROM Donations
    ORDER BY DonerID, Fund, DatePaid, Type;
    Great! Thanks. Wait- one question, will this update each time a new donation/pledge is given?

  6. #6
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    It will display all information in the tables at the time you execute it.

    However, the returned recordset won't be "updatable" (it will be "view only").

  7. #7
    Join Date
    Aug 2003
    Posts
    18
    Originally posted by JTRockville
    It will display all information in the tables at the time you execute it.

    However, the returned recordset won't be "updatable" (it will be "view only").
    So I will just have to run the query again? Okay, I can live with that...

    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •