UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Query To Find Overlapping Date Entries, Access 2010    
 
   
Brandi
post May 12 2017, 06:12 AM
Post#1



Posts: 1,569
Joined: 24-June 04



I have a table where a Begin Date and End Date are recorded for a CaseNumber.

I am trying to find records that have overlapping Dates because there should only be one entry per case for the same day.

Example: CaseNumber 123 Begin Date 1/5/17 End Date 1/10/17 I calculate the number of days using a module that takes out weekends and holidays and this is used to record how many days are spent on a Trial. And there can be multiple date periods like this for a Trial (CaseNumber)

However, someone could enter another date period for example with Begin Date 1/7/17 and End Date 1/15/17 and not realize there are overlapping days that would be recorded twice.

I am trying to figure out if there is a way to pull these issues in a query so that I can report on them.

Thank you.
Brandi
Go to the top of the page
 
LPurvis
post May 12 2017, 06:47 AM
Post#2


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

See if this old thread gives you a point in the right direction. :-)

Cheers

--------------------
Go to the top of the page
 
nvogel
post May 12 2017, 08:21 AM
Post#3



Posts: 811
Joined: 26-January 14
From: London, UK


I'm not sure whether you are looking for overlapping ranges where the case numbers are the same or different. The following example should give you an idea:

SELECT *
FROM YourTable AS t1, YourTable AS t2
WHERE t1.BeginDate <= t2.EndDate
AND t1.EndDate >= t2.BeginDate
AND t1.CaseNumber <> t2.CaseNumber;
Go to the top of the page
 
Brandi
post May 12 2017, 08:43 AM
Post#4



Posts: 1,569
Joined: 24-June 04



I am looking for overlapping dates where the CaseNumber is the same.

I am not good at writing SQL but can understand it when I see it. I will look at these suggestions this weekend and let you know if I am able to get it to work.

Thank you.
Brandi
Go to the top of the page
 
RAZMaddaz
post May 12 2017, 08:55 AM
Post#5


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


Brandi,

Another suggestion....

You could create a Select Query, in which you could Group By the CaseNumber and then Min by the "BeginDate" field and Max by the "EndDate" field.

RAZMaddaz
Go to the top of the page
 
Brandi
post May 12 2017, 08:56 AM
Post#6



Posts: 1,569
Joined: 24-June 04



A query sounds a little easier for me. Sounds like it might work. I'll work on it this weekend.

Thank you all.

Brandi
Go to the top of the page
 
nvogel
post May 12 2017, 09:13 AM
Post#7



Posts: 811
Joined: 26-January 14
From: London, UK


I don't think a simple GROUP BY with MIN and MAX will help you determine whether two date ranges overlap.

To check for overlapping date ranges for the same case number:

SELECT *
FROM YourTable AS t1, YourTable AS t2
WHERE t1.BeginDate <= t2.EndDate
AND t1.EndDate >= t2.BeginDate
AND t1.CaseNumber = t2.CaseNumber;

This query will return at least one row even if there are no overlaps. To avoid that you need to include some field(s) that discriminate one row from another, e.g. a key, something you didn't specify in your original post. For example if X is a key in your table, do this:

SELECT *
FROM YourTable AS t1, YourTable AS t2
WHERE t1.BeginDate <= t2.EndDate
AND t1.EndDate >= t2.BeginDate
AND t1.CaseNumber = t2.CaseNumber
AND t1.X <> t2.X;
Go to the top of the page
 
nvogel
post May 12 2017, 09:13 AM
Post#8



Posts: 811
Joined: 26-January 14
From: London, UK


I don't think a simple GROUP BY with MIN and MAX will help you determine whether two date ranges overlap.

To check for overlapping date ranges for the same case number:

SELECT *
FROM YourTable AS t1, YourTable AS t2
WHERE t1.BeginDate <= t2.EndDate
AND t1.EndDate >= t2.BeginDate
AND t1.CaseNumber = t2.CaseNumber;

This query will return at least one row even if there are no overlaps. To avoid that you need to include some field(s) that discriminate one row from another, e.g. a key, something you didn't specify in your original post. For example if X is a key in your table, do this:

SELECT *
FROM YourTable AS t1, YourTable AS t2
WHERE t1.BeginDate <= t2.EndDate
AND t1.EndDate >= t2.BeginDate
AND t1.CaseNumber = t2.CaseNumber
AND t1.X <> t2.X;
Go to the top of the page
 
Brandi
post May 15 2017, 01:24 PM
Post#9



Posts: 1,569
Joined: 24-June 04



Here is what I tried but it didn't pull any records.

SELECT tblTrialDates.SuprCtNo, tblTrialDates.FormDate, tblTrialDates.CaseNumber, tblTrialDates.SuppressionTrial, tblTrialDates.BeginDate, tblTrialDates.EndDate, [tblTrialDates].[SuprCtNo] & [tblTrialDates].[FormDate] & [tblTrialDates].[CaseNumber] & [tblTrialDates].[SuppressionTrial] AS Expr1
FROM tblTrialDates INNER JOIN tblTrialDates AS tblTrialDates_1 ON (tblTrialDates.SuppressionTrial = tblTrialDates_1.SuppressionTrial) AND (tblTrialDates.CaseNumber = tblTrialDates_1.CaseNumber) AND (tblTrialDates.FormDate = tblTrialDates_1.FormDate) AND (tblTrialDates.SuprCtNo = tblTrialDates_1.SuprCtNo)
WHERE (((tblTrialDates.BeginDate)<=[TblTrialDates_1].[EndDate]) AND ((tblTrialDates.EndDate)>=[tblTrialDates_1].[BeginDate]) AND (([tblTrialDates].[SuprCtNo] & [tblTrialDates].[FormDate] & [tblTrialDates].[CaseNumber] & [tblTrialDates].[SuppressionTrial])<>[tblTrialDates_1].[SuprCtNo] & [tblTrialDates_1].[FormDate] & [tblTrialDates_1].[CaseNumber] & [tblTrialDates_1].[SuppressionTrial]));


tblTrialDates has a key of SuprCtNo + FormDate + CaseNumber + SuppressionTrial.

There can only be one record per SuprCtNo (Prosecutor) on a particular day (FormDate) for a particular CaseNumber for a particular type (Suppression Hearing OR Trial).

Sample data:

SuprCtNo FormDate CaseNumber BeginDate EndDate SuppressionTrial
12345 1/10/2017 ABC 1/5/2017 1/9/2017 S
12345 1/10/2017 ABC 1/5/2017 1/9/2017 T
12345 1/10/2017 ABC 1/6/2017 1/8/2017 T

I would expect 1/6/2017 and 1/7/2017 and 1/8/2017 to be overlapping Dates as related to the T (Trial record) Even though the S (Suppression Hearing) record also overlaps with those dates, they should not be counted because there is only one S (Suppression Hearing) record BUT the SQL above does not find any overlapping records.

BTW - how do I make tabs work when typing my question. It looks like it all runs together in my sample data after I submit.
Thank you for your help.
Brandi
Go to the top of the page
 
Brandi
post May 22 2017, 07:34 PM
Post#10



Posts: 1,569
Joined: 24-June 04



Has anyone read my last post? Does my example make sense?
I read the thread that was suggested above and it is quite confusing to me because I am not a SQL expert.

Can anyone provide a little more direction for me based on my sample data?

Thank you.
Brandi
Go to the top of the page
 
Brandi
post Nov 1 2017, 01:37 PM
Post#11



Posts: 1,569
Joined: 24-June 04



This topic was tabled some time ago, but now I am ready to make a query that pulls overlapping dates from one table.
I did not understand fully the previous suggestions and was unsuccessful at creating a query.
Can I reopen this thread?

tblTrialDates is linked to tblTrialStats by TrialID
tblTrialDates has TrialId and a BeginDate and an EndDate. There can be multiple Begin and End dates for a TrialID.
I need to know if there are overlapping dates for the same TrialID

Sample data:

TrialID BeginDate End Date

123 1/1/2017 1/4/2017
123 1/2/2017 1/10/2017

I would expect the query to return 1/2, 1/3, and 1/4/2017 as overlapping dates.


BTW - how do I make tabs work when typing my question. It looks like it all runs together in my sample data after I submit.
Thank you for your help.
Brandi
Go to the top of the page
 
John Vinson
post Nov 1 2017, 03:33 PM
Post#12


UtterAccess VIP
Posts: 4,143
Joined: 6-January 07
From: Parma, Idaho, US


It's a bit easier to just find Trials where dated overlap. Create a new Query, and add tblTrialDates to the query twice - Access will give the second instance of the table the alias tblTrialDates1. Join the two instances by TrialID.

On the Criteria line under TrialDates1.BeginDate type

<= [TrialDates].[EndDate]

and under TrialDates1.EndDate type

>= [TrialDates].[BeginDate]

Also, under TrialDates1.TrialID put a criterion

> [TrialDates].[TrialID]

This will prevent the same overlap showing up twice - e.g. Trial 123 overlaps with Trial 222, and trial 222 overlaps with Trial 123.

If you want to find all DAYS where there is an overlap - days which are between some BeginDate and EndDate pair, but not equal to either - you'll need an additional table, one containing every date that you want to see (I presume excluding weekends and holidays). Add this table to your query with NO join line, and put a criterion on its datefield of

>= [TrialDates1].[BeginDate] AND <= [TrialDates1].[EndDate]

I'm not completely sure of this last criterion, you may need to fiddle with it. Good luck!

And remember Shakespeare's comment about Time from As You Like It: "Who stays it still withal?" "With lawyers in the vacation; for they sleep between term and term, and then they perceive not how Time moves."

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
Brandi
post Nov 1 2017, 03:59 PM
Post#13



Posts: 1,569
Joined: 24-June 04



Here is the SQL of the suggested query if I did it right.

SELECT tblTrialDates.TrialID, tblTrialDates.BeginDate, tblTrialDates.EndDate, tblTrialDates_1.TrialID, tblTrialDates_1.BeginDate, tblTrialDates_1.EndDate
FROM tblTrialDates LEFT JOIN tblTrialDates AS tblTrialDates_1 ON tblTrialDates.TrialID = tblTrialDates_1.TrialID
WHERE (((tblTrialDates_1.TrialID)>[TrialDates].[TrialID]) AND ((tblTrialDates_1.BeginDate)<=[TrialDates].[EndDate]) AND ((tblTrialDates_1.EndDate)>=[TrialDates].[BeginDate]))
ORDER BY tblTrialDates.BeginDate, tblTrialDates.EndDate;

The query prompts me for tblTrialDates.TrialID, tblTrialDates.BeginDate and tblTrialDates.EndDate NOT Sure why it is prompting me.

I get no result but there should be some overlapping dates on TrialID 17. 10/3/17 and 10/4/17 overlap and 10/11/17 and 10/12/17 overlap..

Here are the records for tblTrialDates

TrialID BeginDate EndDate
2 1/23/2017 1/25/2017
3 3/7/2017 3/7/2017
4 3/27/2017 3/30/2017
5 4/17/2017 4/18/2017
6 12/13/2016 12/15/2016
7 1/4/2017 1/6/2017
8 4/24/2017 4/25/2017
9 5/1/2017 5/5/2017
10 7/17/2017 7/24/2017
11 8/1/2017 8/2/2017
12 5/8/2017 5/11/2017
13 7/10/2017 7/12/2017
16 10/2/2017 10/4/2017
17 10/2/2017 10/4/2017
17 10/11/2017 10/13/2017
17 10/3/2017 10/12/2017

Thank you. Brandi
Go to the top of the page
 
John Vinson
post Nov 1 2017, 05:36 PM
Post#14


UtterAccess VIP
Posts: 4,143
Joined: 6-January 07
From: Parma, Idaho, US


A prompt means that you're including a name which Access can't recognize. I see a couple: try changing the SQL to

SQL
SELECT tblTrialDates.TrialID, tblTrialDates.BeginDate, tblTrialDates.EndDate, tblTrialDates_1.TrialID, tblTrialDates_1.BeginDate, tblTrialDates_1.EndDate
FROM tblTrialDates LEFT JOIN tblTrialDates AS tblTrialDates_1 ON tblTrialDates.TrialID = tblTrialDates_1.TrialID
WHERE (((tblTrialDates_1.TrialID)>[tblTrialDates].[TrialID]) AND ((tblTrialDates_1.BeginDate)<=[tblTrialDates].[EndDate]) AND ((tblTrialDates_1.EndDate)>=[tblTrialDates].[BeginDate]))
ORDER BY tblTrialDates.BeginDate, tblTrialDates.EndDate;


I'm not sure at all why it would prompt for tblTrialDates.TrialID, tblTrialDates.BeginDate and tblTrialDates.EndDate unless there is no table named tblTrialDates or that table does not include those fields.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
Brandi
post Nov 2 2017, 07:53 AM
Post#15



Posts: 1,569
Joined: 24-June 04



Thank you. That SQL took care of the prompts, but there is still no result with the records listed in the previous post.
Something tricky about this. I'll have to think about it. I'm not sure about the criteria under TrialID because the TrialID must be the same as in the sample where there are 3 records with TrialID 17. Those are the records I want it to catch.
I removed the criteria under TrialID and now it is showing me multiple records. Here is the resulting data. This is the data I would expect, now I just need to figure out maybe in a Step2 Query, how to pull out that for TrialID17 (or any resulting records where there are more than one of the same TrialID, that there might be overlapping dates. In this sample, ultimately, it should tell me that for TrialID 17, 10/3/17 and 10/4/17 and 10/11/17 and 10/12/17 are duplicate days.

ALSO IS THERE A WAY TO MAKE TABS WORK IN THESE POSTS?

tblTrialDates.TrialID tblTrialDates.BeginDate tblTrialDates.EndDate tblTrialDates_1.TrialID tblTrialDates_1.BeginDate tblTrialDates_1.EndDate
2 1/23/2017 1/25/2017 2 1/23/2017 1/25/2017
3 3/7/2017 3/7/2017 3 3/7/2017 3/7/2017
4 3/27/2017 3/30/2017 4 3/27/2017 3/30/2017
5 4/17/2017 4/18/2017 5 4/17/2017 4/18/2017
6 12/13/2016 12/15/2016 6 12/13/2016 12/15/2016
7 1/4/2017 1/6/2017 7 1/4/2017 1/6/2017
8 4/24/2017 4/25/2017 8 4/24/2017 4/25/2017
9 5/1/2017 5/5/2017 9 5/1/2017 5/5/2017
10 7/17/2017 7/24/2017 10 7/17/2017 7/24/2017
11 8/1/2017 8/2/2017 11 8/1/2017 8/2/2017
12 5/8/2017 5/11/2017 12 5/8/2017 5/11/2017
13 7/10/2017 7/12/2017 13 7/10/2017 7/12/2017
16 10/2/2017 10/4/2017 16 10/2/2017 10/4/2017
17 10/2/2017 10/4/2017 17 10/2/2017 10/4/2017
17 10/2/2017 10/4/2017 17 10/3/2017 10/12/2017
17 10/3/2017 10/12/2017 17 10/3/2017 10/12/2017
17 10/3/2017 10/12/2017 17 10/2/2017 10/4/2017
17 10/3/2017 10/12/2017 17 10/11/2017 10/13/2017
17 10/11/2017 10/13/2017 17 10/11/2017 10/13/2017
17 10/11/2017 10/13/2017 17 10/3/2017 10/12/2017

Brandi
Go to the top of the page
 
John Vinson
post Nov 2 2017, 04:55 PM
Post#16


UtterAccess VIP
Posts: 4,143
Joined: 6-January 07
From: Parma, Idaho, US


Tabs don't work here, alas, even in code blocks. Maybe one of the UA experts can correct me.

The TrialID criterion should have worked. Please copy and paste the entire SQL of the query that's working so far, and the one with which you tried the TrialID. What is the Primary Key of this table?

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
Brandi
post Nov 7 2017, 11:56 AM
Post#17



Posts: 1,569
Joined: 24-June 04



Thanks for staying with me on this!

There is no Primary Key as this is a detail table. It is related to a Primary table called tblTrialStats which has an Autonumber field called TrialID as the Primary Key.

tblTrialDates which I am using to discover overlapping dates is related by TrialID where there can be many related records in tblTrialDates.
Here is the SQL that gets me the resulting records above.
SELECT tblTrialDates.TrialID, tblTrialDates.BeginDate, tblTrialDates.EndDate, tblTrialDates_1.TrialID, tblTrialDates_1.BeginDate, tblTrialDates_1.EndDate
FROM tblTrialDates LEFT JOIN tblTrialDates AS tblTrialDates_1 ON tblTrialDates.TrialID = tblTrialDates_1.TrialID
WHERE (((tblTrialDates_1.BeginDate)<=[tblTrialDates].[EndDate]) AND ((tblTrialDates_1.EndDate)>=[tblTrialDates].[BeginDate]))
ORDER BY tblTrialDates.TrialID, tblTrialDates.BeginDate, tblTrialDates.EndDate;


When I include the criteria under tblTrialDates_1, I get no resulting records. Here is the SQL

SELECT tblTrialDates.TrialID, tblTrialDates.BeginDate, tblTrialDates.EndDate, tblTrialDates_1.TrialID, tblTrialDates_1.BeginDate, tblTrialDates_1.EndDate
FROM tblTrialDates LEFT JOIN tblTrialDates AS tblTrialDates_1 ON tblTrialDates.TrialID = tblTrialDates_1.TrialID
WHERE (((tblTrialDates_1.TrialID)>[tblTrialDates].[TrialID]) AND ((tblTrialDates_1.BeginDate)<=[tblTrialDates].[EndDate]) AND ((tblTrialDates_1.EndDate)>=[tblTrialDates].[BeginDate]))
ORDER BY tblTrialDates.TrialID, tblTrialDates.BeginDate, tblTrialDates.EndDate;

The resulting records above in previous post, could get me what I need if there is some way to split each begin end date series into separate days. Then I could just count the occurrences of the same date - maybe?

Thanks.
Brandi
Go to the top of the page
 
Brandi
post Nov 29 2017, 08:03 PM
Post#18



Posts: 1,569
Joined: 24-June 04



I am attaching a db with tblTrialDates and a query that returns the records from tblTrialDates. Here are the records. TrialId 17 has 3 different begin and end date records. I need to know that for TrialID 17, overlapping dates are 10/3, 10/4, 10/11, and 10/12 but I'm not sure how to get that from this data.
Thank you.
Brandi

TrialID BeginDate EndDate
2 1/23/2017 1/25/2017
3 3/7/2017 3/7/2017
4 3/27/2017 3/30/2017
5 4/17/2017 4/18/2017
6 12/13/2016 12/15/2016
7 1/4/2017 1/6/2017
8 4/24/2017 4/25/2017
9 5/1/2017 5/5/2017
10 7/17/2017 7/24/2017
11 8/1/2017 8/2/2017
12 5/8/2017 5/11/2017
13 7/10/2017 7/12/2017
16 10/2/2017 10/4/2017
17 10/2/2017 10/4/2017
17 10/11/2017 10/13/2017
17 10/3/2017 10/12/2017
Attached File(s)
Attached File  TrialStatsOverlappingDates.zip ( 1.52MB )Number of downloads: 3
 
Go to the top of the page
 
MadPiet
post Nov 29 2017, 08:30 PM
Post#19



Posts: 2,257
Joined: 27-February 09



Are you checking for any overlaps for any trial dates, or only for the same TrialID?

Fixed your DB for the folks here... Made a local table of the Trials stuff...

Here's an article by Allen Browne (who has apparently quit doing Access consulting =( ) that looks at previous records.

If you only want Trial #17 (because it has multiple records), you can eliminate the other stuff using a totals query.

CODE
SELECT tblTrialDates.TrialID
FROM tblTrialDates
GROUP BY tblTrialDates.TrialID
HAVING (((Count(tblTrialDates.BeginDate))>1));


Then you can just search that trial for overlapping dates... Of course, if you mean overlapping dates regardless of trial number, then the above query would be irrelevant.
Go to the top of the page
 
Brandi
post Nov 29 2017, 09:32 PM
Post#20



Posts: 1,569
Joined: 24-June 04



Just for the same TrialID. I used your code below and it does indeed single out TrialID 17. But if I did it correctly that is all it does. It does not tell me which individual days occur more than once within TrialID 17.
I need a way for a query to tell me 10/3, 10/4, 10/11, and 10/12 have more than one occurrence within the Begin and End Dates for TrialID 17. Is that possible?
Thank you.
Brandi
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 06:36 PM