asked on

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!

nico5038

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)

tgambee

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.

nico5038

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)

davelowndes

ASKER

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

nico5038

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)

nico5038

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)

ASKER CERTIFIED SOLUTION

Netminder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial