UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Series Of Dates, Label As 1,2,3... From A Start Date, Access 2016    
 
   
daschrislydon
post Dec 26 2017, 02:33 PM
Post#1



Posts: 101
Joined: 28-August 15



Hello

Wasn't quite sure how to describe this in the topic title. Don't think I've done it justice!

In a table I have all the dates between 1 Jan 2017 and 31 Dec 2030.
I would like to be able to group/rank/label the dates with a number based on a start date.
(This is for helping to calculate bank interest, by the way, if that's of any use).

So, one interest rate might start on 9 Jan 2017, and be paid monthly, but calculated daily.
So I'd like to label all the dates from 9 Jan to 8 Feb with 1, then 9 Feb to 8 Mar with 2, then 9 Mar to 8 Apr with 3, etc. etc.

Like so:
































































9-Jan-171
10-Jan-171
11-Jan-171
12-Jan-171
13-Jan-171
14-Jan-171
15-Jan-171
16-Jan-171
17-Jan-171
18-Jan-171
19-Jan-171
20-Jan-171
21-Jan-171
22-Jan-171
23-Jan-171
24-Jan-171
25-Jan-171
26-Jan-171
27-Jan-171
28-Jan-171
29-Jan-171
30-Jan-171
31-Jan-171
1-Feb-171
2-Feb-171
3-Feb-171
4-Feb-171
5-Feb-171
6-Feb-171
7-Feb-171
8-Feb-171
9-Feb-172
10-Feb-172
11-Feb-172
12-Feb-172
13-Feb-172
14-Feb-172
15-Feb-172
16-Feb-172
17-Feb-172
18-Feb-172
19-Feb-172
20-Feb-172
21-Feb-172
22-Feb-172
23-Feb-172
24-Feb-172
25-Feb-172
26-Feb-172
27-Feb-172
28-Feb-172
1-Mar-172
2-Mar-172
3-Mar-172
4-Mar-172
5-Mar-172
6-Mar-172
7-Mar-172
8-Mar-172
9-Mar-173
10-Mar-173
11-Mar-173
12-Mar-173


I feel like this COULD be done using SQL in a query. With a function or a subquery or some combo.

Can't quite wrap my head around it yet though.

(or perhaps someone who knows more about bank interest can point me to already existing functions to help with this. I feel like they must exist!)

Thanks, as always, for any tips/advice

Chris


Go to the top of the page
 
mike60smart
post Dec 26 2017, 03:56 PM
Post#2


UtterAccess VIP
Posts: 12,576
Joined: 6-June 05
From: Dunbar,Scotland


Hi Chris

The mathematical formula for calculating EMIs is: EMI = [P x R x (1+R)^N]/[(1+R)^N-1], where P stands for the loan amount or principal, R is the interest rate per month [if the interest rate per annum is 11%, then the rate of interest will be 11/(12 x 100)], and N is the number of monthly instalments.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 07:56 PM