periodical report lists database design advice

[p27br]

hello,

i am looking for advice on designing my database : What I want to achieve is the following :
I have a table which contains information related to my reports. These reports need to be created (refreshed) at a certain date depending on it's periodicity (weekly, monthly,quarterly). I'd like my database to create the lists of reports that need to be created for each period (week 1...52, month 1...12...).
has anyone done somezthing similar ? How can it be automated ?
I'd imagine a button 'PRINT LIST OF REPORTS TO BE PRODUCED FOR WEEK N' or something like that.

thanks in adance for any suggestions.

Paul

 

[fneily]

In your table that has the report information, you could have a field that has the date of last creation. You also have a field that states if it's weekly, monthly, quarterly. You can then add a field that will hold the next date for creation. This date will be calculated. On the field that has the date of last creation, on it's AfterUpdate event, you can place the following to calculate the next date for creation:

Private Sub Date_Last_Cald_AfterUpdate()
If [Cal_Frequency] = "Annually" Then
[Next_Cal_Date] = [Date_Last_Cald] + 365
ElseIf [Cal_Frequency] = "Bi-Annually" Then
[Next_Cal_Date] = [Date_Last_Cald] + 730
ElseIf [Cal_Frequency] = "Daily" Then
[Next_Cal_Date] = [Date_Last_Cald] + 1
ElseIf [Cal_Frequency] = "Monthly" Then
[Next_Cal_Date] = [Date_Last_Cald] + 30
ElseIf [Cal_Frequency] = "Quarterly" Then
[Next_Cal_Date] = [Date_Last_Cald] + 90
ElseIf [Cal_Frequency] = "Semi-Annually" Then
[Next_Cal_Date] = [Date_Last_Cald] + 182
ElseIf [Cal_Frequency] = "Weekly" Then
[Next_Cal_Date] = [Date_Last_Cald] + 7

End If
Exit Sub
End Sub

Then you can create any report you want by using parameter queries on the next date for creation field.

Neil

 

[p27br]

thanks neil,

that sounds like a good starting point. I thank you for your idea.

regards

Paul