Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# OT: Storing and calculating date series

Posted on 2002-04-19
Medium Priority
247 Views
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
Question by:davelowndes
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 54

Expert Comment

ID: 6954385
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

LVL 1

Expert Comment

ID: 6954401
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

LVL 54

Expert Comment

ID: 6954450
Hi tgambee,

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

Nic;o)
0

Author Comment

ID: 6955381
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

LVL 54

Expert Comment

ID: 6955925
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

LVL 54

Expert Comment

ID: 7119845
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
next seven days.

Nic;o)
0

LVL 5

Accepted Solution

Netminder earned 0 total points
ID: 7134595
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
###### Suggested Courses
Course of the Month11 days, 12 hours left to enroll