Report Pending Payments with mixed Monthly & Weekly intervals <p>We are working with a database that tracks payment plans for patients. Some patients pay monthly, some weekly, and some every other week.</p> <p>The PaymentsSchedule table fields are Patient, Frequency, NextDueDate, PaymentsRemaining, Amount. Once a payment is processed the NextDueDate and PaymentsRemaining fields are updated so that at any given time there is only one record representing all future payments for a given payment plan.</p> <p>The report we want to generate would show all payments expected within the next month. Something like this:</p> <pre><code>Report Date: 3/1/2014 Patient Frequency Next Date Pmts Left Amount 01 Monthly 3/01/2014 5 $100 02 Weekly 3/02/2014 3 $30 03 Weekly 3/02/2014 7 $25 04 Bi-Weekly 3/03/2014 4 $75 02 Weekly 3/09/2014 2 $30 03 Weekly 3/09/2014 6 $25 02 Weekly 3/16/2014 1 $30 03 Weekly 3/16/2014 5 $25 04 Bi-Weekly 3/17/2014 3 $75 03 Weekly 3/23/2014 4 $25 03 Weekly 3/30/2014 3 $25 </code></pre> <p>I could set up 5 different queries for the 5 date possibilities (weekly) of a payment plan for the next 31 days, pull them together in a UNION and then filter out dates that are not in my time horizon but I would like to find a simpler solution.</p> <p>Your help is much appreciated.</p> <p>Thanks,</p> <p>Jim S</p>