re-occuring dates

[raj chahal]

Hi there,

I'm creating a access database for 'events'. I have created a field for
'date of event', the asp then checks if this date has lapsed, if so it
doen't show the event. All works fine.
The only problem is how do I manage dates that re-occur ? i.e every tuesday
or every 1st tuesay of the month.
The re-occuring event would have some general blurb that applies to all the
dates, but on some occations I may need to add new unique info for
forthcoming events. ie special guest. How would I design the dtatabase
structure and asp code around this senario ?

I hope someone can help with some principles here, Thanks

Raj

 

[Tom B]

This can be a pain, and you have a few options.

You can decide to populate the next X times when it's created. So in your
example if it's every Tuesday then you fill in the all of the tuesdays for
the next X years.

You can populate as it's requested. When someone asks for Next June's
schedule, the recurring events are checked and the Tuesdays are filled in
for June of next year.

 

[Aaron Bertrand - MVP]

You could have a table (called tablename) like this:

EventName VARCHAR
dt DATETIME
DayOfWeek TINYINT
Recurring BIT (0/1)

And a dates table (imaginatively called DatesTable) that is populated with
ALL potential dates, say from 20030101 through 20291231:

dt DATETIME
DayOfWeek TINYINT

Then you could have something like this in your data table:

'Weekly Meeting'
'20030923'
3 -- Tuesday
1

'One Time Meeting'
'20031002'
5 -- Thursday
0

Then let's say you want to get all the events between September 28th and
October 5th:

SELECT EventName, dt
FROM tablename
WHERE (dt >= #20030928# AND dt <#20031006#)
OR (recurring = 1 AND DayOfWeek IN (SELECT DayOfWeek FROM DatesTable
WHERE
(dt >= #20030928# AND dt <#20031006#) AND dt >= tablename.dt))


Not sure if Access can handle the nested subqueries, but that's one quick
and dirty way to approach it in SQL Server.