Perhaps, another alternative ( no additional table)
The logic tried to be used here :
1) We get the MinStartDate & MaxEndDate for each ArticleID
2) We then check each of the date from MinStartDate to MaxEndDate in each of the Task record for that ArticleID, to check whether it lies in the Date range in that record.
3) If it is found in any record, we exit the loop & move on to check the next date.
4) If it is not found, we increment the i counter in the else.
5) In the end, we compare the i counter with the Number of Task records for that ArticleID. If it is the same, it means the Date does not exist in any of the records. We subtract 1 from the Original Total No off Tasks.
6) We repeat this for each date for that article.
Save this in the previously attached db & Run it.
Code:
Public Function GetArticlesNoOffDays()
Dim dbs As Database
Set dbs = CurrentDb
' Loop through the records from Table tblTasks to get MinStartDate & MaxEndDate for an ArticleID
Dim strsqlTasks As String
Dim rsta As DAO.Recordset
Dim rstb As DAO.Recordset
Dim TheArticleID As String
Dim TheStartDate As Date
Dim TheEndDate As Date
Dim strsqlSelectTasks As String
strsqlTasks = "SELECT ArticleID, Min(StartDate) AS MinOfStartDate, Max(EndDate) AS MaxOfEndDate FROM tblTasks GROUP BY tblTasks.ArticleID;"
Set rsta = dbs.OpenRecordset(strsqlTasks, dbOpenDynaset)
Do While Not rsta.EOF
TheArticleID = rsta![ArticleID]
TheStartDate = rsta![MinOfStartDate]
TheEndDate = rsta![MaxOfEndDate]
TheMaxNoOffDays = DateDiff("d", TheStartDate, TheEndDate) + 1
TheNoOffDays = TheMaxNoOffDays
'Debug.Print TheArticleID
'Debug.Print "The Start Date : " & TheStartDate
'Debug.Print "The End Date : " & TheEndDate
'Debug.Print "The Max No Off Days : " & TheMaxNoOffDays
'Debug.Print "The Current Total No Off Days : " & TheNoOffDays
Do While TheStartDate <= TheEndDate
' i is the counter set to check if a Date is not there in all the records
Dim i
i = 0
strsqlSelectTasks = "select ArticleID,StartDate,EndDate from tblTasks where ArticleID = " & TheArticleID & ";"
Set rstb = dbs.OpenRecordset(strsqlSelectTasks, dbOpenDynaset)
Dim strsqlSelectArticleTasksCount
Dim rstc As DAO.Recordset
Dim TheNoOffRecords
strsqlSelectArticleTasksCount = "select Count(*) as TheNoOffTasksPerArticle from tblTasks where ArticleID = " & TheArticleID & ";"
Set rstc = dbs.OpenRecordset(strsqlSelectArticleTasksCount, dbOpenDynaset)
TheNoOffRecords = rstc![TheNoOffTasksPerArticle]
Do While Not rstb.EOF
TaskStartDate = rstb![StartDate]
TaskEndDate = rstb![EndDate]
If ((TheStartDate >= TaskStartDate) And (TheStartDate <= TaskEndDate)) Then
Exit Do
Else
'Build a test here, if the No of times Not Between the Dates is equal to the record count, then
' subtract 1 later from TheNoOffDays
i = i + 1
End If
rstb.MoveNext
Loop
If i = TheNoOffRecords Then
TheNoOffDays = TheNoOffDays - 1
End If
TheStartDate = DateAdd("d", 1, TheStartDate)
Loop
Debug.Print TheArticleID & " - " & TheNoOffDays
rsta.MoveNext
Loop
MsgBox "Completed successfully."
End Function
Have not thought about efficiency of present vis-a-vis previous currently?
Thanks