Solved

OT: Storing and calculating date series

Posted on 2002-04-19
7
241 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
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 …

829 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