Looking for overlap in dates between two records

[kagoodwin13]

I have a database of logistics contracts. Each record is a shipping lane, with origin, destination, shipping rate between origin and destination, and effective dates for each rate.



I'm trying to formulate a query to return when the same shipping lane has overlapping effective dates. This happens when an original contract has one date, but later an amendment to the contract changes the rate.

For instance, if I'm shipping between Oshkosh, WI and Hackensack, NJ with Joe's Trucking, I may have one contract with an effective rate valid from 1/1/2010-12/31/2011. Later, Joe and I need to amend the contract to adjust for higher fuel prices, so I have a new rate between 7/1/2010-12/31/2011.

pbaldy, if you're reading this, yes, I did refer to your overlap page on this matter. However, the string you recommend is a string I use in query to return valid contracts between those two dates. I think my particular problem is adding another layer of complexity.

I referred to this website for help: http://allenbrowne.com/appevent.html and I'm using the following string:

NoClash: ([Table_1].[Effective Date] >= [Table].[End Date]) Or 
([Table_1].[End Date] <= [Table].[Effective Date]) Or 
([Table].[Carrier] <> [Table_1].[Carrier]) Or 
([Table].[Origin City] = [Table_1].[Origin City])

In SQL it looks like so:

SELECT [Table].ID, [Table].[Origin City], [Table].[Dealer City], [Table].Carrier, [Table].[Effective Date], [Table].[End Date], [Table_1].ID, [Table_1].[Origin City], [Table_1].[Dealer City], [Table_1].Carrier, [Table_1].[Effective Date], [Table_1].[End Date], ([Table_1].[Effective Date]>=[Table].[End Date]) Or ([Table_1].[End Date]<=[Table].[Effective Date]) Or ([Table].[Carrier]<>[Table_1].[Carrier]) Or ([Table].[Origin City]=[Table_1].[Origin City])=[Table_1].[Carrier] AS NoClash
FROM [Table], [Table] AS [Table_1]
WHERE (((([Table_1].[Effective Date]>=[Table].[End Date]) Or ([Table_1].[End Date]<=[Table].[Effective Date]) Or ([Table].[Carrier]<>[Table_1].[Carrier]) Or ([Table].[Origin City]=[Table_1].[Origin City])=[Table_1].[Carrier])=False));

The result I'm getting is not what I need though: it spits back every record of the table on the left side compared to record 1 on the right side. Basically that query is bunk.

Any other ideas?

[kagoodwin13]

I solved this myself. The code from Allen Browne was correct. I just had to figure out which fields to query, and which to make = or <> (not equal). Lots of trial and error.

[orange]

You want to post your solution and/or link to Allen Browne's good info? Others may read this in search of an answer.

[kagoodwin13]

Both the link and code are referenced in the original post.

[orange]

Great. Thanks