I manage the production of over 150 reports. Some of which are due
daily, weekly, bi-weekly & monthly. (some reports have multiple due dates for the current month.) I have the production schedule in a table with multiple DueDate columns: DueDate1, DueDate2, DueDate3, etc. Some reports are due every business day of the week.
Question #1: Should I display a column for each due date (31 columns accross?) or show a duplicate row for that report for each day the report is due? ( Sales_Report, 5/20/13, Completed
Sales_Report, 5/28/13, Pending
Sales _Report, 5/31/13, Pending
Question#2: is there a formula that can be written to autopopulate the duedate fields for every instance and every month? ie. Report 1 due every Mon, Wed, Fri (5/13/13, 5/15/13, 5/17)
I am currently putting these dates in manually every month and it would be a pain to continue that given that our reporting production schedule will only increase.
I would store the dates in rows. You could use a function to determine day of week (or you could do so in a query) and run an append query to add the dates you want reports.
Function WhatDayIsIt(StartDate As Date, EndDate As Date, rptType as String)
Dim DayDate As Date
For DayDate = StartDate To EndDate
Select Case Weekday(DayDate)
Case 2 'Monday
If Dcount("*","tblReports","[DateDue]=#" & DayDate & "# AND [rptName]='" & rptType & "'") = 0 then
Docmd runsql "Insert Into tblReports (rptName, DueDate) SELECT DayDate AS DueDate, rptType AS rptName;"