MS Access Funct for RollOut Schedules (1 Viewer)

[Chouz06]

Hi all,

I was wondering if someone can assist me with a function I can use in my query which will allow me to calculate the release amount of a prepayment each month until the prepayments dates elapse.

Example:
current period date: 31 Dec 2020

Prepayment Value $170
Term Start Date: 07 April 2020
Term End Date: 06 April 2021

So , as we are in December the amount to be recognised would be equal to the % of the term that has elapsed. I.e. 31 Dec 2020 minus 07 April 2020 = 269/365 days have to be recognised. Thus (269/365)*$170 = $125

I will require a field which tells me the remainder left to recognise I.e $45

then I need fields which lets call “Month +1, month+2 and Month+3” etc
In this case month +1 will need the extra amount that will release this will be $14.
this needs to go on until the $45 draws down to 0 in Month+4

Can anyone assist me with this as when I get to month+4 (April 2021) I run into a problem with the calculation as the term end date is at the first week of April , so I only will need to recognise a Small portion of that month

 

[MajP]

Have you come up with a solution. I do think this can be done in a query, but it may take a little work. I am assuming each month is paid at last day of month. To do this in a query you would have to build a big table of the last day of each month for many years out. This can be done in code very easily to build that table. Then You would have to build a cartesian query for each month except the first and last and do the calculation. Then you need a separate first month query and a separate last month query. Then you can union the three together. In my mind this is workable and not as bad as it sounds.
I did it in code and saved the answers instead to a table. I will look at the query possibility.
If I start with this
tblAccounts tblAccounts

AccountID	PrepaymentValue	TermStartDate	TermEndDate
1​	$179.00​	4/7/2020​	4/6/2021​

My code creates the following
tblPaymentSchedules tblPaymentSchedules

PaymentDate	MonthlyPayment	TotalPayment	AccountID_FK
4/30/2020​	$11.31​	$11.31​	1​
5/31/2020​	$14.75​	$26.55​	1​
6/30/2020​	$14.26​	$41.31​	1​
7/31/2020​	$14.75​	$56.55​	1​
8/31/2020​	$14.75​	$71.80​	1​
9/30/2020​	$14.26​	$86.55​	1​
10/31/2020​	$14.75​	$101.79​	1​
11/30/2020​	$14.26​	$116.55​	1​
12/31/2020​	$14.75​	$131.79​	1​
1/31/2021​	$14.75​	$147.04​	1​
2/28/2021​	$13.28​	$160.80​	1​
3/31/2021​	$14.75​	$176.05​	1​
4/6/2021​	$2.46​	$179.00​	1​

Is that correct and would that suffice? You can build a schedule for all accounts. If so I can send the code. You would need a form to run the code for a given account or build for all accounts.

 

[MajP]

I did it with SQL and it was not trivial.

SELECT tblaccounts.accountid,
       tblaccounts.prepaymentvalue,
       Dateserial(Year([termstartdate]), Month([termstartdate]) + 1, 0) AS
       PaymentDate,
       [prepaymentvalue] * ( [paymentdate] - [termstartdate] ) / (
       [termenddate] - [termstartdate] )                                AS
       TotalPayment,
       [totalpayment]                                                   AS
       MonthlyPayment
FROM   tblaccounts
UNION
SELECT tblaccounts.accountid,
       tblaccounts.prepaymentvalue,
       tbldates.dtmdate                  AS PaymentDate,
       [prepaymentvalue] * ( [dtmdate] - [termstartdate] ) / (
       [termenddate] - [termstartdate] ) AS TotalPayment,
       [prepaymentvalue] * ( [paymentdate] - Dateserial(Year([paymentdate]),
                                             Month(
                                             [paymentdate]), 1) ) / (
       [termenddate] - [termstartdate] ) AS MonthlyPayment
FROM   tblaccounts,
       tbldates
WHERE  (( ( tbldates.dtmdate ) > Dateserial(Year([tblaccounts].[termstartdate]),
                                 Month([tblaccounts].[termstartdate]) + 1, 0)
          AND ( tbldates.dtmdate ) < [tblaccounts].[termenddate] ))
UNION
SELECT tblaccounts.accountid,
       tblaccounts.prepaymentvalue,
       tblaccounts.termenddate           AS PaymentDate,
       [prepaymentvalue] * ( [paymentdate] - [termstartdate] ) / (
       [termenddate] - [termstartdate] ) AS TotalPayment,
       [prepaymentvalue] * ( [termenddate] - Dateserial(Year([termenddate]),
                                             Month(
                                             [termenddate]), 1) ) / (
       [termenddate] - [termstartdate] ) AS MonthlyPayment
FROM   tblaccounts;

 

[Chouz06]

Hi thank you so much for your help! Let me try this out and see if I get the intended results.
if I continue to struggle would it be useful if I could show you via a zoom call?

hopefully I will be able to resolve my issue with your above solution. My initial one I was getting Tied up in circular references.
Thanks again !

 

[MajP]

1. I did all dates from 2019 to 2050. If you need earlier then you will have to add. The code is thre to add dates.
2. The query will work. If you already have a table with those fields
Prepayment Value $170
Term Start Date: 07 April 2020
Term End Date: 06 April 2021
You can make a query and alias the query and the field names so that my query can use it. If not you will have to recreate the query which will be a PITA. At least you can see how to do it. I also left the code if you want to do it in code and persist the answer in a table.

 

[CJ_London]

I have a simpler solution (well, less code)

SELECT
    [TStart]+[counter] AS PayDate,
    CCur((Day([paydate]+(([counter]<31)*Day([tstart]))))/([tend]-[tstart])*[prepaymentvalue]) AS MPay,
    CCur([counter]/([tend]-[tstart])*[prepaymentvalue]) AS TPay

FROM tmp, admCount

WHERE (
            ((admCount.Counter) Between 1 And [tend]-[tstart]) AND ((Month([tstart]+[counter]))<>Month([tstart]+[counter]+1)))
     OR (((admCount.Counter) Between 1 And [tend]-[tstart]) AND (([TStart]+[counter])=[tend])
)

ORDER BY [counter];

PayDate	MPay	TPay
30/04/2020​	£11.31​	£11.31​
31/05/2020​	£15.24​	£26.55​
30/06/2020​	£14.75​	£41.31​
31/07/2020​	£15.24​	£56.55​
31/08/2020​	£15.24​	£71.80​
30/09/2020​	£14.75​	£86.55​
31/10/2020​	£15.24​	£101.79​
30/11/2020​	£14.75​	£116.55​
31/12/2020​	£15.24​	£131.79​
31/01/2021​	£15.24​	£147.04​
28/02/2021​	£13.77​	£160.80​
31/03/2021​	£15.24​	£176.05​
06/04/2021​	£2.95​	£179.00​
​	£179.00​	​

This requires a table called admcounter, populated with a single field called ctr with the numbers 0-9 (so ten records)

then have this query which I call admCount

SELECT CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000) AS [Counter]
FROM admCounter AS singles, admCounter AS tens, admCounter AS hundreds, admCounter AS thousands;

this query has a lot of uses - this version will handle up to 10,000 iterations which I have found is sufficient for most uses

change your table and field names to suit

 

[CJ_London]

@MajP - not sure if your calc is correct? last month $176.05+$2.46=$178.51, not $179.00

but I also have some rounding differences, June for example, but only for 1p/1c and balances out in the end

 

[MajP]

@CJ_London
I am subtracting the 6Apr - 1Apr and getting five days. So I am letting them skate on that last day I guess it should really be (6-1) + 1 so it is 6 days.
But that surely simplifies things. I do not even and looking at the trees and not the forest. I probably should not be subtracting at all.

 

[Chouz06]

I have a simpler solution (well, less code)

SELECT
    [TStart]+[counter] AS PayDate,
    CCur((Day([paydate]+(([counter]<31)*Day([tstart]))))/([tend]-[tstart])*[prepaymentvalue]) AS MPay,
    CCur([counter]/([tend]-[tstart])*[prepaymentvalue]) AS TPay

FROM tmp, admCount

WHERE (
            ((admCount.Counter) Between 1 And [tend]-[tstart]) AND ((Month([tstart]+[counter]))<>Month([tstart]+[counter]+1)))
     OR (((admCount.Counter) Between 1 And [tend]-[tstart]) AND (([TStart]+[counter])=[tend])
)

ORDER BY [counter];

PayDate	MPay	TPay
30/04/2020​	£11.31​	£11.31​
31/05/2020​	£15.24​	£26.55​
30/06/2020​	£14.75​	£41.31​
31/07/2020​	£15.24​	£56.55​
31/08/2020​	£15.24​	£71.80​
30/09/2020​	£14.75​	£86.55​
31/10/2020​	£15.24​	£101.79​
30/11/2020​	£14.75​	£116.55​
31/12/2020​	£15.24​	£131.79​
31/01/2021​	£15.24​	£147.04​
28/02/2021​	£13.77​	£160.80​
31/03/2021​	£15.24​	£176.05​
06/04/2021​	£2.95​	£179.00​
​	£179.00​	​

This requires a table called admcounter, populated with a single field called ctr with the numbers 0-9 (so ten records)

then have this query which I call admCount

SELECT CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000) AS [Counter]
FROM admCounter AS singles, admCounter AS tens, admCounter AS hundreds, admCounter AS thousands;

this query has a lot of uses - this version will handle up to 10,000 iterations which I have found is sufficient for most uses

change your table and field names to suit

Hi thank also for your input, however i am no Access expert by any means and am slightly stumped on what "admCount" field is/how its calculated along with the [counter] field.

If possible do you have a mock DBase with this in so i can visually understand what the mechanism/logic is?

Thank you

 

[CJ_London]

here you go

 

[Chouz06]

Thanks so much both for your help! Greatly appreciated. @MajP @CJ_London

 

[MajP]

@Chouz06
If having any trouble, it may help if you pass your real database table then we can configure the query with real table names and field names and you should be able to just drop in the query.

 

[Chouz06]

@MajP - thanks for that offer. If i do i most certainly will!. Frustrating thing I'm facing at the moment is running the query. Have over 23K rows which are going calculate at least 10 extra rows for the future release dates. I've put my first query into a made table to see if it speeds it up but still looks pretty slow

 

[CJ_London]

Have over 23K rows which are going calculate at least 10 extra rows for the future release dates.

that is not many records and I have to ask, why do you want a query to create circa 230k records?
I can understand needing to know on an account by account basis, or all accounts for a specific month, but all of them? at the same time?

I've put my first query into a made table to see if it speeds it up but still looks pretty slow

not sure what this means, the query works off tables, not other queries

if this is intended as a 'view' from which you will extract an account or a month, then you are going about it the wrong way. That is an excel method which doesn't sit well with database methods.

Apply a criteria for an account or month and see what performance is like.

Other things to consider - are the relevant fields indexed?

 

[Chouz06]

Right, I have attached an extract of those 23K rows which is my dataset. I've had to delete majority of the rows as its just struggles to attach. Each row has a transaction "DocumentNo". Each will have its own start and end date and those are the ones i require a rollout schedule for according to the period im currently working in (in this case Dec20 - 31/12/20) and the start and end dates.


The aim is to run everything out of the access database - i.e clean up of my data is done there. All the data i have now is clean but the final step is just to create this rollout schedule that i can report to management groups - the end goal was to make a table with this data and then just link it to a pivot in Excel for the relevant people to look at.

 

[CJ_London]

suggest adapt your query to be a crosstab with the month ends as the column headers. Note your transaction numbers and account ids are not unique.

Also consider using winzip if the file is large