Solved

OT: Storing and calculating date series

Posted on 2002-04-19
7
242 Views
Last Modified: 2012-08-13
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
Comment
Question by:davelowndes
7 Comments
 
LVL 54

Expert Comment

by:nico5038
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

by:tgambee
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

by:nico5038
ID: 6954450
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:davelowndes
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

by:nico5038
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

by:nico5038
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
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

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

Netminder
CS Moderator
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question