[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

Overlapping Date/Time Entries in Microsoft Access Records

I have a huge table of progress notes that track who created the progress note, the date and time of the progress note and the client the progress note was written for. The problem is that some of the clients have overlapping progress notes for the same date and time, sometimes by the same staff person and sometimes by a different staff person. I need to be able to identify what progress notes have overlapping date and time entries for the same client. For instance, if a staff person create a note for Client A for 12/05/05 from 7 a.m. to 10 a.m. and then, for some reason, entered a second progress note for Client A for 12/05/05 from 9 a.m. to 11:00 a.m., I need to identify so it can be corrected. I've consolidated the records by Client, Staff Member and date and time. Now I need to contrast the records for each client and find the overlaps. Help!
1 Solution
Why not just check if there is a note after the staff member enters the date, time, and client. Create a button that will activate the note area if there are no conflicting entries and if there are you could a) have the staff member enter different date and time or b) import the existing data and update the entry with the current info.
headbumpAuthor Commented:
Unfortunately its not my application. All I've inherited are the overlapping records and I need to find a way to weed through 400,000 plus records to find the overlaps.
Try this.  DTS is the start datetime field, DTE is the end datetime field.

Select d.StaffID, a.ClientID, a.DTS, a.DTE, OverLapDTS, OverLapDTE from
(Select a.StaffID, a.ClientID, a.DTS, a.DTE, (Select b.DTS from myTable AS b where b.DTS Between a.DTS and a.DTE and b.StaffID=a.StaffID, and b.ClientID=a.ClientID) as OverLapDTS, (Select Min(c.DTS) from myTable AS c where c.DTS Between a.DTS and a.DTE and c.StaffID=a.StaffID, and c.ClientID=a.ClientID) as OverLapDTE, from MyTable AS a Order By a.DTS, a.DTE) AS d Where IsNull(d.OverLapDTS & d.OverLapDTE)=False;

The first subquery contains two subsubqueries which determine if any record is between the start and end of the test record.  It fills in an overlapstart or an overlapend if so.  Then the outer query selects any record from the inner query which contains an overlapstart or and overlapend.  This may run slowly, but when you think of all the comparisons that have to be made record by record ... It gave me a headache just writing it;-)  

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now