Tracking Duplicate File Review Time (1 Viewer)

JithuAccess

Member
Local time
Today, 16:18
Joined
Mar 3, 2020
Messages
297
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

Super Moderator
Staff member
Local time
Today, 18:18
Joined
Jan 23, 2006
Messages
15,364
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

Member
Local time
Today, 16:18
Joined
Mar 3, 2020
Messages
297
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:

1622479006787.png


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
 

Attachments

  • 1622478851136.png
    1622478851136.png
    4.6 KB · Views: 355

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:18
Joined
May 21, 2018
Messages
8,463
To verify no individual reviewer can have overlap periods, but another review can overlap another reviewer?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:18
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 16:18
Joined
Mar 3, 2020
Messages
297
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:

1622481907603.png
 

Attachments

  • Reviewed Hours.accdb
    952 KB · Views: 463

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:18
Joined
May 21, 2018
Messages
8,463
Test this.

Here is the main code, but required some helper code.
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.
 

Attachments

  • MajPReviewed Hours.accdb
    1,004 KB · Views: 510

JithuAccess

Member
Local time
Today, 16:18
Joined
Mar 3, 2020
Messages
297
Test this.

Here is the main code, but required some helper code.
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

You've got your good things, and you've got mine.
Local time
Today, 18:18
Joined
May 21, 2018
Messages
8,463
Make sure to give it a good test of all the conditions you can think of to include the boundary conditions
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:18
Joined
May 7, 2009
Messages
19,169
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

You've got your good things, and you've got mine.
Local time
Today, 18:18
Joined
May 21, 2018
Messages
8,463
@arnelgp Thanks, that is much simpler.
@JithuAccess The previous logic is overkill and you can simplify the logic with
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 & "')"
  '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

You've got your good things, and you've got mine.
Local time
Today, 18:18
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 16:18
Joined
Mar 3, 2020
Messages
297
@arnelgp Thanks, that is much simpler.
@JithuAccess The previous logic is overkill and you can simplify the logic with
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 & "')"
  '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

You've got your good things, and you've got mine.
Local time
Today, 18:18
Joined
May 21, 2018
Messages
8,463
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.

Code:
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).
Code:
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

Code:
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

strIDstrNamestrReviewed ByT1StartDateTimeT1EndDateTimeT2StartDateTimeT2EndDateTime
100First Name, Last NameAdmin 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​
100Admin 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
 

Attachments

  • MajPReviewed Hours (2).accdb
    1.7 MB · Views: 472

JithuAccess

Member
Local time
Today, 16:18
Joined
Mar 3, 2020
Messages
297
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.

Code:
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).
Code:
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

Code:
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

strIDstrNamestrReviewed ByT1StartDateTimeT1EndDateTimeT2StartDateTimeT2EndDateTime
100First Name, Last NameAdmin 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​
100Admin 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
 

Users who are viewing this thread

Top Bottom