Consulting

Results 1 to 2 of 2

Thread: 3 level report

  1. #1
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location

    3 level report

    Hello..

    I am struggling with what should be a fairly simple construct, but for some reason it isn't working. What I am working on is a Donations database. To break it down I have 3 basic tables which contain data as follows...

    Table Join Fields Other Fields
    Donors DonorID Name1, Name2, Address...
    Donations DonationBatchID Total, Payment Method, Date...
    Items DonBatchID (same as above) Amount, BudgetDesignation...
    Receipt ReceiptID RcptNo, IssueDate, EligibleAmt


    My question has to do with setting up a 3 tier report. The receipt itself contains the address, names and total that I want to get from a Sum field.
    I want to be able to select a donor account and press a button to issue a year end receipt tax deductible receipt.

    The first tier will contain the information on the Receipt. Most of this is found in Donors, other than a few in Donations.
    ............The second tier will contain general information about each donation in the receipt, followed by a...
    .......................Third tier which has another record for each additional itemized gift designation.

    So far i used a query to gather all the info together and created the report using a criteria function to get DonationBatchID. Since DonBatchID is the same number, it seems to work, except that it isn't showing the second and third donation covered by the same receipt. It only shows the first. I get the problem. The criteria needs to move up a level. I need to search for the donor, and then all donations related to that donor should show up. But when I used a criteria function to get the DonorID, the report showed blank.

    Advice? I am sure you will need more info, but I might need to do that tomorrow.
    Thanks

    Gary

  2. #2
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    YAY... I've managed to get the receipt working as I wanted. I did three things...

    1. Changed some field names so as to remove all possibility of confusion on joins.
    2. Changed the report to follow the DonorID.
    3. Relocated the data for Tier 2 into the header for Tier 2. (oops - this I believe to have been the real issue)


    I wanted to get records only from Donor Z...
    The Receipt was to show above in first third of page. (easy part)
    --------------------------------------------------------------------
    Followed by a dotted line and then details from which the year end receipt came.
    Details:
    Z's first donation from Table.Donations against the margin (Tier 2)
    Details for that donation from Table.Items (Tier 3)
    Z's second donation against margin (T2)
    Details for that donation from Table.Items (Tier 3)
    Etc for all records that match for the year.

    Spent a lot of time trying to make it work, came in frustration, and today, after a few hours work, figured it out.
    Thanks anyway. Am posting answer in case it helps someone else.

    Gary
    Attached Images Attached Images

Posting Permissions

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