Tracking Duplicate File Review Time

[JithuAccess]

Hello Guys,

I am trying to Track Reviewed Hours of Files by various Departments. There are 3 Levels of Reviewing, 1) Admin Assistant 2) Business Analyst and 3) Manager. I want to eliminate Duplicate Entry. For Example an Admin Asst Reviewed a File on 31-May-2021 from 8:15 AM to 4:30 PM. Again by mistake, if the Admin Asst do the Data Entry like 31-May-2021 4:00 PM to 4:30 PM I want to display a Message Box. But if the Admin Assistant do the Data Entry like 4:31 PM to 5:01 PM it should save in the Table. And if the Admin Asst enter the time like 4:00 PM to 4:45 PM I want to alert the Admin Asst that they have already Entered time up to 4:30 PM on 31-May-2021 for that particular File.

Is it a Doable in Access? If Yes, could you guys please let me know how?

Thanks

 

[jdraw]

Can you describe what each of the reviews is for? Is there some sort of Status check/update after a review?
How does Data Entry fit into a Review?
You may consider a process involving
-which review is involved
-who did the review
-start timestamp of review
-end timestamp of review
-comments if further action is required??
-some date info concerning created timestamp and modified timestamp

We really need more details on the process(es) involved and some completion/status info.

 

[JithuAccess]

Can you describe what each of the reviews is for? Is there some sort of Status check/update after a review?
How does Data Entry fit into a Review?
You may consider a process involving
-which review is involved
-who did the review
-start timestamp of review
-end timestamp of review
-some date info concerning created timestamp and modified timestamp

We really need more details on the process(es) involved and some completion/status info.

Hi,

I have created a separate Table and Form for storing Review Hours. So 1 File will be Reviewing by three levels by Admin Asst, Business Analyst and Manager. I want to prevent Duplicate Data Entry. By Duplicate I mean, a File should not be Reviewed by Admin Asst. for the Same Day and Time. For Example, if a File is Reviewing by Admin Asst and the Entry is like:

On the Next Day if an Admin Asst Enter this same details, I want to display a Message Box and stop that Entry. At the same time, this should be saved in the Table if this Files was Reviewed by Business Analyst or Manager on this same date and Time.

Thanks

 

[MajP]

To verify no individual reviewer can have overlap periods, but another review can overlap another reviewer?

 

[JithuAccess]

To verify no individual reviewer can have overlap periods, but another review can overlap another reviewer?

Yes. Exactly

 

[MajP]

This can be done, but if you post your db it will be a lot easier to provide a working example. Also if you have not done this already please ensure there are no spaces in the names of any fields or tables.

 

[JithuAccess]

This can be done, but if you post your db it will be a lot easier to provide a working example. Also if you have not done this already please ensure there are no spaces in the names of any fields or tables.

Please find the Attachment



This is Datasheet view of my Table:

 

[MajP]

Test this.

Here is the main code, but required some helper code.

Public Function IsInvalidPeriod(fileID As String, reviewerID As String, ReviewStart As Date, ReviewEnd As Date) As Boolean
  Dim strmsg As String
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim strStart As String
  Dim strEnd As String
 
  strStart = CSql(ReviewStart, sdt_date)
  strEnd = CSql(ReviewEnd, sdt_date)
 
  strSql = "Select * from qryValidate WHERE (strID = '" & fileID & "' AND ReviewerID = '" & reviewerID & "')"
  'Case 1 Period starts before existing start and stops after existing start
  strSql = strSql & " AND ((dtmReviewStart >= " & strStart & " AND dtmReviewStart <= " & strEnd & ")"
  'Case 2 Period start before existing end and stops after existing end
  strSql = strSql & " OR (dtmReviewEnd >= " & strStart & " AND dtmReviewEnd <= " & strEnd & ")"
  'Case 3 Period start and end between existing start and end
  strSql = strSql & " OR (dtmReviewStart <= " & strStart & " AND dtmReviewEnd >= " & strEnd & "))"
  Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    If strmsg = "" Then
      strmsg = "Your review period from " & ReviewStart & " to " & ReviewEnd & " by " & reviewerID & " comflicts with existing reviews"
      strmsg = strmsg & vbCrLf & "There is already a review from " & rs!dtmReviewStart & " To " & rs!dtmReviewEnd
    Else
      strmsg = strmsg & vbCrLf & "and from " & rs!dtmReviewStart & " To " & rs!dtmReviewEnd
    End If
    rs.MoveNext
  Loop
  If strmsg <> "" Then
    IsInvalidPeriod = True
    MsgBox strmsg
  End If
End Function

Please do yourself a favor and remove the spaces from your field names.

 

[JithuAccess]

Test this.

Here is the main code, but required some helper code.

Public Function IsInvalidPeriod(fileID As String, reviewerID As String, ReviewStart As Date, ReviewEnd As Date) As Boolean
  Dim strmsg As String
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim strStart As String
  Dim strEnd As String

  strStart = CSql(ReviewStart, sdt_date)
  strEnd = CSql(ReviewEnd, sdt_date)

  strSql = "Select * from qryValidate WHERE (strID = '" & fileID & "' AND ReviewerID = '" & reviewerID & "')"
  'Case 1 Period starts before existing start and stops after existing start
  strSql = strSql & " AND ((dtmReviewStart >= " & strStart & " AND dtmReviewStart <= " & strEnd & ")"
  'Case 2 Period start before existing end and stops after existing end
  strSql = strSql & " OR (dtmReviewEnd >= " & strStart & " AND dtmReviewEnd <= " & strEnd & ")"
  'Case 3 Period start and end between existing start and end
  strSql = strSql & " OR (dtmReviewStart <= " & strStart & " AND dtmReviewEnd >= " & strEnd & "))"
  Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    If strmsg = "" Then
      strmsg = "Your review period from " & ReviewStart & " to " & ReviewEnd & " by " & reviewerID & " comflicts with existing reviews"
      strmsg = strmsg & vbCrLf & "There is already a review from " & rs!dtmReviewStart & " To " & rs!dtmReviewEnd
    Else
      strmsg = strmsg & vbCrLf & "and from " & rs!dtmReviewStart & " To " & rs!dtmReviewEnd
    End If
    rs.MoveNext
  Loop
  If strmsg <> "" Then
    IsInvalidPeriod = True
    MsgBox strmsg
  End If
End Function

Please do yourself a favor and remove the spaces from your field names.

Thanks a lot for your Help.

 

[MajP]

Make sure to give it a good test of all the conditions you can think of to include the boundary conditions

 

[arnelgp]

there are only 2 (general) conditions for Overlapping:

T2.START_DATE < T1.END_DATE OR T1.START_DATE < T2.END_DATE

this is the same principle for Overlapping rectangles.

 

[MajP]

@arnelgp Thanks, that is much simpler.
@JithuAccess The previous logic is overkill and you can simplify the logic with

Public Function IsInvalidPeriod(fileID As String, reviewerID As String, ReviewStart As Date, ReviewEnd As Date) As Boolean
  Dim strmsg As String
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim strStart As String
  Dim strEnd As String
 
  strStart = CSql(ReviewStart, sdt_date)
  strEnd = CSql(ReviewEnd, sdt_date)
 
  strSql = "Select * from qryValidate WHERE (strID = '" & fileID & "' AND ReviewerID = '" & reviewerID & "')"
  'Period2 must End after Start of Period 1, And End of Period 1 must be greater than the Start of Period 2
  strSql = strSql & " AND ( " & strEnd & " >= dtmReviewStart AND dtmReviewEnd >= " & strStart & ")"
 
  Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    If strmsg = "" Then
      strmsg = "Your review period from " & ReviewStart & " to " & ReviewEnd & " by " & reviewerID & " comflicts with existing reviews"
      strmsg = strmsg & vbCrLf & "There is already a review from " & rs!dtmReviewStart & " To " & rs!dtmReviewEnd
    Else
      strmsg = strmsg & vbCrLf & "and from " & rs!dtmReviewStart & " To " & rs!dtmReviewEnd
    End If
    rs.MoveNext
  Loop
  If strmsg <> "" Then
    IsInvalidPeriod = True
    MsgBox strmsg
  End If
End Function

 

[MajP]

Can we use a Query instead of this Excellent Code (Sorry I am a Beginner in VBA and while I was testing the Code I am getting Error Messages like "Invalid Use of Null" while I was Navigating between the Records and I am not sure why I am getting this Error)?

I did not put in any error checking to ensure you have an ID, Reviewer, Start date and time, and end date and time. You probably need a little extra code. This function can be actually be much easier done in a query to show overlaps. Most of that code is extra to check your data, make a message. I will send the query.

 

[JithuAccess]

@arnelgp Thanks, that is much simpler.
@JithuAccess The previous logic is overkill and you can simplify the logic with

Public Function IsInvalidPeriod(fileID As String, reviewerID As String, ReviewStart As Date, ReviewEnd As Date) As Boolean
  Dim strmsg As String
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim strStart As String
  Dim strEnd As String

  strStart = CSql(ReviewStart, sdt_date)
  strEnd = CSql(ReviewEnd, sdt_date)

  strSql = "Select * from qryValidate WHERE (strID = '" & fileID & "' AND ReviewerID = '" & reviewerID & "')"
  'Period2 must End after Start of Period 1, And End of Period 1 must be greater than the Start of Period 2
  strSql = strSql & " AND ( " & strEnd & " >= dtmReviewStart AND dtmReviewEnd >= " & strStart & ")"

  Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    If strmsg = "" Then
      strmsg = "Your review period from " & ReviewStart & " to " & ReviewEnd & " by " & reviewerID & " comflicts with existing reviews"
      strmsg = strmsg & vbCrLf & "There is already a review from " & rs!dtmReviewStart & " To " & rs!dtmReviewEnd
    Else
      strmsg = strmsg & vbCrLf & "and from " & rs!dtmReviewStart & " To " & rs!dtmReviewEnd
    End If
    rs.MoveNext
  Loop
  If strmsg <> "" Then
    IsInvalidPeriod = True
    MsgBox strmsg
  End If
End Function

Thanks a lot

 

[MajP]

This is much easier if you have a single field for date time instead of a date field and time field. To make it a single field in a query I made this function to make it easier.

Public Function DateTime(TheDate As Date, TheTime As Date) As Date
  DateTime = DateValue(TheDate) + TimeValue(TheTime)
End Function

I broke this into pieces. I dropped the table twice into a query and joined by Reviewer. I also converted the date times into a single date and time. This query will match every record for a given reviewer to every other record for that reviewer. So if a review has 3 records then it creates 9 matches (each record with every other).

SELECT T1.strid,
       T1.strname,
       T1.[strreviewed by],
       Datetime([T1].[datreview start date], [T1].[review start time]) AS
       T1StartDateTime,
       Datetime([T1].[datreview end date], [T1].[review end time])     AS
       T1EndDateTime,
       Datetime([T2].[datreview start date], [T2].[review start time]) AS
       T2StartDateTime,
       Datetime([T2].[datreview end date], [T2].[review end time])     AS
       T2EndDateTime
FROM   [tblreviewed hours] AS T1
       INNER JOIN [tblreviewed hours] AS T2
               ON T1.[strreviewed by] = T2.[strreviewed by]
ORDER  BY T1.strid,
          T1.[strreviewed by],
          Datetime([T1].[datreview start date], [T1].[review start time]),
          Datetime([T1].[datreview end date], [T1].[review end time]),
          Datetime([T2].[datreview start date], [T2].[review start time]),
          Datetime([T2].[datreview end date], [T2].[review end time]);

Now you need to filter out the same record matched to itself (t1Start = T2Start AND T1End = T2END) and include records where T2Start <= T1End and T2E>= T1S

SELECT qrycartesianreview.strid,
       qrycartesianreview.strname,
       qrycartesianreview.[strreviewed by],
       qrycartesianreview.t1startdatetime,
       qrycartesianreview.t1enddatetime,
       qrycartesianreview.t2startdatetime,
       qrycartesianreview.t2enddatetime
FROM   qrycartesianreview
WHERE  ( ( ( qrycartesianreview.t2startdatetime ) <> [t1startdatetime] )
         AND ( ( qrycartesianreview.t2enddatetime ) <> [t1enddatetime] ) )
       AND ( ( ( qrycartesianreview.t2startdatetime ) <= [t1enddatetime] )
             AND
( ( qrycartesianreview.t2enddatetime ) >= [t1startdatetime] ) );

Just remember the results are a little confusing when you first look at it because you list all conflicts on the left side
qryConflicts qryConflicts

strID	strName	strReviewed By	T1StartDateTime	T1EndDateTime	T2StartDateTime	T2EndDateTime
100	First Name, Last Name	Admin Assistant	5/31/2021 8:15:00 AM​	5/31/2021 4:15:00 PM​	5/31/2021 8:30:00 AM​	5/31/2021 4:30:00 PM​
100		Admin Assistant	5/31/2021 8:30:00 AM​	5/31/2021 4:30:00 PM​	5/31/2021 8:15:00 AM​	5/31/2021 4:15:00 PM​

So that is one conflict, but each conflict is made up of two records

 

[JithuAccess]

This is much easier if you have a single field for date time instead of a date field and time field. To make it a single field in a query I made this function to make it easier.

Public Function DateTime(TheDate As Date, TheTime As Date) As Date
  DateTime = DateValue(TheDate) + TimeValue(TheTime)
End Function

I broke this into pieces. I dropped the table twice into a query and joined by Reviewer. I also converted the date times into a single date and time. This query will match every record for a given reviewer to every other record for that reviewer. So if a review has 3 records then it creates 9 matches (each record with every other).

SELECT T1.strid,
       T1.strname,
       T1.[strreviewed by],
       Datetime([T1].[datreview start date], [T1].[review start time]) AS
       T1StartDateTime,
       Datetime([T1].[datreview end date], [T1].[review end time])     AS
       T1EndDateTime,
       Datetime([T2].[datreview start date], [T2].[review start time]) AS
       T2StartDateTime,
       Datetime([T2].[datreview end date], [T2].[review end time])     AS
       T2EndDateTime
FROM   [tblreviewed hours] AS T1
       INNER JOIN [tblreviewed hours] AS T2
               ON T1.[strreviewed by] = T2.[strreviewed by]
ORDER  BY T1.strid,
          T1.[strreviewed by],
          Datetime([T1].[datreview start date], [T1].[review start time]),
          Datetime([T1].[datreview end date], [T1].[review end time]),
          Datetime([T2].[datreview start date], [T2].[review start time]),
          Datetime([T2].[datreview end date], [T2].[review end time]);

Now you need to filter out the same record matched to itself (t1Start = T2Start AND T1End = T2END) and include records where T2Start <= T1End and T2E>= T1S

SELECT qrycartesianreview.strid,
       qrycartesianreview.strname,
       qrycartesianreview.[strreviewed by],
       qrycartesianreview.t1startdatetime,
       qrycartesianreview.t1enddatetime,
       qrycartesianreview.t2startdatetime,
       qrycartesianreview.t2enddatetime
FROM   qrycartesianreview
WHERE  ( ( ( qrycartesianreview.t2startdatetime ) <> [t1startdatetime] )
         AND ( ( qrycartesianreview.t2enddatetime ) <> [t1enddatetime] ) )
       AND ( ( ( qrycartesianreview.t2startdatetime ) <= [t1enddatetime] )
             AND
( ( qrycartesianreview.t2enddatetime ) >= [t1startdatetime] ) );

Just remember the results are a little confusing when you first look at it because you list all conflicts on the left side
qryConflicts qryConflicts

strID	strName	strReviewed By	T1StartDateTime	T1EndDateTime	T2StartDateTime	T2EndDateTime
100	First Name, Last Name	Admin Assistant	5/31/2021 8:15:00 AM​	5/31/2021 4:15:00 PM​	5/31/2021 8:30:00 AM​	5/31/2021 4:30:00 PM​
100		Admin Assistant	5/31/2021 8:30:00 AM​	5/31/2021 4:30:00 PM​	5/31/2021 8:15:00 AM​	5/31/2021 4:15:00 PM​

So that is one conflict, but each conflict is made up of two records

Thanks so much