simplifying a query with SQL

[Pitlochry1]

Please help
I am trying to replace dozens of queries that I created through design view
with either only a dozen queries or if possible two.
I have a database that tracks donations by DonorNo, Occurring, DateRecd,
Duration, GA and Notes.
With the DateRecd, Occurring and Duration I am able to list expected
donations but using design view I end up duplicating criteria e.g
DateRecd Between >=#01/01/2008# and <=#31/01/2008#
Occurring 1
(frequency it occurs in a year ie annually, 2-biannually, 4 quarterly,
12-monthly)
Duration Is not Null (ensures that only the initial starting payment is
selected)
That is one criteria which I would mutate and have another one to get all
expected donations in the occurring in the month for Biannually, another one
for Quarterly and another one for monthly and then the same again 2009, 2010,
2011, 2012 for future usage of query.
I have created one of these longwinded queries for each month.
Is there an easier way so that I can select all entries that are due to be
recieved in a given month?

 

[Allen Browne]

You need a table of pledges (what donors are supposed to do), as well as the
table of actual donations (what they actually did.) The pledge table will
have fields like this:
- PledgeID Autonumber primary key
- DonorID Number relates to your table of donors
- PledgeAmt Currency how much each time
- PledgeStart Date/Time when the first pledge is due
- NumPayts Number how many times. (Null if open-ended.)
- PeriodFreq Number
- PeriodTypeID Text

For PeriodTypeID, use a combo that allows values that can be used in a
DateAdd() expression, e.g. "d", "m", "q", "yyyy". Using PeriodFreq and
PeriodTypeID together, you can then specify things like every 30 days, or
every 1 month, or every 2 years, or ...

As to how to generate predicted income for the future, take a look at this
article:
Recurring Events
at:
http://allenbrowne.com/AppRecur.html
It illustrates how to create a cartesian product query that gives you a
record for each time the recurring pledge falls due in the future. You can
then filter the query to show only those pledge payments that are due in a
particular date range.

The article also talks about handling exceptions. You may want to just
ignore this, but it does give you the ability to cancel or reschedule a
particular pledge payment (e.g. where the person is unable to contribute
this year, but still hopes to keep their commitment next year.)