Adding Sequential Dates to table 2

[puforee]

Good afternoon and Happy New Year to everyone.

I must have cleared my memory over the holidays...I knew how to do this at one point.....but....

I have a table of sequential dates....I want to add more sequential dates to the table starting the day after the current MAX date in the table.

So, I would like to add, let's say, 700 sequential dates to the table after the last date currently in the table (12/31/2010).

Any help out there please?

Thanks,

 

[puforee]

I am glad to see my question generated a stimulated conversation. I have learned from this.

In my actual DB, created quite a while ago, I am not sure why I used a date table, I just know I had to update it to support my user. I will be looking at the code/db more carefully to see if I can improve my work.

Thanks again to all who have voiced their ideas here.

 

[MajP]

Michael,
Was not trying to come up with an example that you did not answer. As I stated my example was unclear, it was supposed to support Duane's scenario.

For instance if you have a table of patient visits with a admit and discharge dates. You can use the table of dates with the visit table to get the number of patients at any given date or range of dates.

Although not very clear, my example was supposed to be the desired output (not input) based on Duane's scenario.

1/1/2011
Patient A
Patient B
Patient C
1/2/2011
Patient A
Patient C
Patient D
....
1/31/2011
Patient A
Patient G

Sorry for the confusion.

Duane,
I looked at some of my dbs and I realize I use this concept for many things where the input is continous but the reporting is in discrete increments. You may be able to do some of this in pure sql, but it gets long and painful to write. I have a failure tracking database for equipment that records down time start and end. I have to generate the time periods to show what is up/down during discrete 1 hour increments.

 

[dhookom]

Great thread...

I researched this a while back for SQL Server and calendar tables. I found an article Why should I consider using an auxiliary calendar table? that describes the function of a calendar table and the types of questions it can help you answer.

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

Thanks to duane for the reference. I have reviewed the code & discussion and can say there does seem to be some value to the rationale.

I have never (to the best recollection of my aging / failing memory) been tasked with providing a set of sequential dates without other data being related. In fact, my experience has been more to reporting ONLY dates which have relevance to the data of interest. But I can see the advantage of the table of dates in generating the contigious sequence without related data.

A, perhaps, more common request / requirement from my experience was the example of reporting the dates "of every third Friday".

I would take issue with a few of the items included in the actual calendar and / or touted as 'advantages'. Principally, the inclusion of the Hoildays in the base calendar seems, to me, a large waste. My rationale being that EVERY organization I have worked with 'customizes' their holidays, sometimes to the extreme. One (extreme?) instance was the 'observance' of at least five (minor?) holidays in the Christmas / New Years period! The rationale of the company was that many employees took the time off anyway, which made production quite difficult, so the arrangement made production planning much easier and pleased the employees!. Further, I see only a very small penalty for maintaining the holiday list seperatly.

I also wonder why one would include the Fiscal Year, but not the Fiscal Quarter?

In my 'review', I also added a "Week of the month' field. I recognise this is / can be an issue a-la the ISO week of the year, but (again from my experience) I have had several to numerous occassions to need this information.

Thanks, again to duane and MajP in particular for helping me ubderstand their points of view.


MichaelRed