Overlapping Date/Time Entries in Microsoft Access Records

Posted on 2006-06-08
Last Modified: 2012-05-05
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!
Question by:headbump
    LVL 6

    Expert Comment

    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.

    Author Comment

    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.
    LVL 44

    Accepted Solution

    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;-)  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now