AnsweredAssumed Answered

Getting monthly & YTD totals

Question asked by CraigAlderson on Jun 15, 2016
Latest reply on Jun 15, 2016 by CraigAlderson

Sorry, I'm having a bit of a brain freeze and could use some help. My database tracks individuals who make several payments each month. I need to display individual payments, monthly cumulative, and YTD cumulative on one page. I'd like to sort each category in descending order so that the most recent payments, months, and years are at the top. So, like this:


Payment                                             Monthly total          YTD

Apr 2016     5.00                Apr 2016         5.00               43.00

Mar 2016     7.00               Mar 2016         12.00               38.00

Mar 2016     5.00               Feb 2016          5.00               26.00

Feb 2016     5.00               Jan 2016          21.00               21.00

Jan 2016     3.00

Jan 2016     12.00

Jan 2016     6.00


I know that's going to need at least two portals. I have one table for people and another for payments, linked in one-to-many on ID#. I suspect I'll need a join table between these two tables to get the monthly and yearly cumulatives. I've managed to get some of this data by adding calculation and summary fields to the payments table, but nothing has worked satisfactorily so far.


Your ideas? Let me know if you need additional information. Thanks.