[JJolin]

Problem: Reporting on adjusted gifts for past time frames. Once a gift is adjusted you can’t report on the previous information very easily. This will be an issue for all reports.

If you want to get the pledge balance by fund as of 3/31/2006, then you need to find all the adjustments that have been done after that date. Since gifts can be adjusted more than once you must make sure you find the first adjustment to the gift after the end date of the report.

Easiest Solution: Either keep a copy of your db from certain dates (year-end, etc.), or make sure not to enter adjustments until you have reported on a period. If that is not possible then see below.

My Solution: In Access using the direct ODBC connection I write the following queries

1st Query: Gift Table – All gifts in a range (limit gift type or other data as needed)

2nd Query: Link GiftAdjustment table to GiftPreviousSplit table to Gift table by GiftID and also link the Gift Adjustment table to the Gift table_1 by the AdjustmentId and choose the start date of adjustments that you don’t want to show in your report. If you wanted to report thru 3-31-2006 then you would look for the first adjustment to any gift on or after 4-1-2006. Access SQL below:


SELECT dbo_GiftAdjustment.GiftId, dbo_GiftPreviousSplit.FundId AS PreviousFundID, dbo_GiftPreviousSplit.CampaignId AS PreviousCampaignID, dbo_GiftPreviousSplit.AppealId AS PreviousAppealID, dbo_GiftPreviousSplit.Amount AS PreviousAmt, dbo_GIFT_1.DTE AS GiftDate, First(dbo_GIFT.DTE) AS AdjustmentDate
FROM ((dbo_GiftAdjustment INNER JOIN dbo_GiftPreviousSplit ON dbo_GiftAdjustment.GiftId = dbo_GiftPreviousSplit.GiftId) INNER JOIN dbo_GIFT AS dbo_GIFT_1 ON dbo_GiftPreviousSplit.GiftId = dbo_GIFT_1.ID) INNER JOIN dbo_GIFT ON dbo_GiftAdjustment.AdjustmentId = dbo_GIFT.ID
GROUP BY dbo_GiftAdjustment.GiftId, dbo_GiftPreviousSplit.FundId, dbo_GiftPreviousSplit.CampaignId, dbo_GiftPreviousSplit.AppealId, dbo_GiftPreviousSplit.Amount, dbo_GIFT_1.DTE
HAVING (((First(dbo_GIFT.DTE))>#4/1/2006#));

The second query will give you all the adjustments which will throw off your reports. Pull the Previous amount and fund ( or campaign or appeal info) into output of query.

3rd Query: Link the first two queries and use an IIF statement to replace the gift amount with the previous gift amount if previous gift amount <> null.

You can now write a report off the 3rd query and it will give you the totals you are looking for (before the adjustment).