Get next time payment is due

[Montana]

I have a nonprofit organization that has both donors and members who need notification of when they said they wanted to send money. There is a wide variety of times they have said they will send the money-monthly, quarterly, semi-annually, and annually. What can I use to know who said they would send money. Can I calculate a date off of the last money received? I can use a query to establish that but, I envisioned a query that could be run on any day to see if anyone owed money. Thanks for your assistance.

 

[schirambira]

send reply

I have a nonprofit organization that has both donors and members who need notification of when they said they wanted to send money. There is a wide variety of times they have said they will send the money-monthly, quarterly, semi-annually, and annually. What can I use to know who said they would send money. Can I calculate a date off of the last money received? I can use a query to establish that but, I envisioned a query that could be run on any day to see if anyone owed money. Thanks for your assistance.

Can I calculate a date off of the last money received? I can use a query to establish that but, I envisioned a query that could be run on any day to see if anyone owed money. Thanks for your assistance.[/QUOTE]

 

[neileg]

I assume you have a table for donor details and a table for donations. I assume you have a field in your donor record that holds the frequency. I assume you have a date in the donations table so you know when the last donation was paid.

Create a totals query based on the donations table using Max on the date field that retrieves the donor ID and the date. This will extract the last donation that each donor has made. Create another query that joins your donor table to the totals query you just made and retrieve the Donor ID and the payment frequency from the donor table, and the last date from the totals query. Then add the period implicit in the frequency to the date of the last donation in a calculated field. Add a criterion to this calculated field of <Date().