[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

OT: Storing and calculating date series

Not exactly Access but that's what I'm going to implement it in. I'm looking for a way (schema) to store date series information. I.e. I have transactions that occur monthly (on a set date), weekly, 2 weekly (fortnightly), twice a month, every second month, one-off, etc. Standard automated bank payment-type stuff. My questions are:

- what schema (good for Access) can I use to store payment information like this?
- how can I:
a) retrieve a future date value based on an input date and a "schedule" record holding the above information
b) create a series of dates based on the same?

Thanks!
0
davelowndes
Asked:
davelowndes
1 Solution
 
nico5038Commented:
You'll need to have a table with:
StartDate and
Period
Based on these two you can insert future rows, assuming you know how far in the future they are needed.

A point of concern can be the intermediate change of the period. I can imagine that future payments will have to be deleted when the period is changed.
I would probably only create one payment "in advance" and would delete that one when the period is changed.

Creating a function to return the NextDate based on the last StartDate and the period will be the best way to go.
This function can be used in another function that's processing all last payed payments and (re-)building the future rows.

Getting the idea?

Nic;o)
0
 
tgambeeCommented:
We have an implimented system like this for donation pledges.  

We have a table with the pledge definition:
- pledge id (PK)
- periodic amount
- frequency
- first scheduled date
- etc.

we then build a table of scheduled payments:
- pledge id (FK)
- plegde payment id (PK)
- scheduled amount
- scheduled date
- payment date
- payment amount

we then apply payments as they come in to the second table as well as the actual gift table.
0
 
nico5038Commented:
Hi tgambee,

Please read the Tips on Comments and answers below...

Your proposed answer makes this Q less visible and will force davelowndes to take action.....

Nic;o)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
davelowndesAuthor Commented:
tgambee your answer is too abstract. I realise I need to keep track of start date, frequency, amount (and an option to build table of future payments). My question is HOW.

Ie. define frequency. How do I represent "Every second week" vs "Every 3rd Month" vs "20th of each Month (start date may be another date)"

I'd also like to pass in a date and see if it "hits" given a start date and frequency. I.e. if payments are every second week starting 13 April 2001 does 2 Dec 2001 fall on a payment day?

I will double the points to 600 (post another question for expert) if I can get this. I can probably work this out myself over the long term but I'd like an answer quick and I'm sure someone's had to do this sort of thing before.

Thanks
0
 
nico5038Commented:
Hi,

I see you definitively need a "frequency function".
Basically you need for the period two fields:
1) The number of
2) The frequency
So e.g.
1 week
1 month
1 day
1 quarter
1 year

The "hard" part are the fixed daynumbers, you could use:
2 MonthFixedDay
However you'll need the actual daynumber within the month and days > 29 have to be solved for february...

I would start by tracing the requirements for the "frequency function".
Just make a list of frequencies needed and specially look into the "fixed days" needs.
Also the conditions when a frequency is changed should be checked. Easiest is to start a new sequence.

Globally the function will have the following parameters:
1) StartDay
2) FreqNumber
3) FreqPeriod
4) NextPeriodDate
5) FindFrequencyDate

Clear sofar?

Nic;o)
0
 
nico5038Commented:
for davelowndes

No comment has been added for the last two months.
So it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
NetminderCommented:
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now