Provided by Allen Browne.
You are handed a table of events and asked to identify which ones overlap. The idea is straight forward, but expressing it in terms a computer can understand can be evasive. If - like most Access users - you are not an expert in SQL, you may be surprised at how simply and elegantly SQL solves this problem, once you have figured out how to wrap your mind around it.
In pursuit of a solution, you will face these questions:
The same questions arise in other applications with start and end date/times. For example, a table of appointments for various staff would have exactly the same structure, with StaffID replacing LocationID.
In this example, our table is named tblEvent, and has this structure:
|Field Name||Data Type||Description|
|EventName||Text||The name of this event|
|Start||Date/Time||Date and time this event starts|
|End||Date/Time||Date and time this event ends|
|LocationID||Number||Long Integer, relates to Location table|
Two table properties are also set:
Validation Rule: ([Start] Is Null) Or ([End] Is Null) Or ([End]>=[Start]) Validation Text: The event cannot end before it starts.
The first twist is that each record must be compared against all others in the table to see if there is a clash. A query has only one current record, so how can you compare two different ones?
The trick is to use two copies of the same table in one query. Follow these steps:
Note: Each clash will appear twice in this query. For example, if event 8 clashes with event 34, the query will also report that event 34 clashes with event 8.
We now have a query that compares every event against every other event. Now how do we identify the clashes?
Ready for a challenge? Try to define the conditions where two events clash. Essentially we need to know if there is any time between the Start and End of one event that is also between the Start and End of another event. The Start time alone is no clue. If Event A starts before Event B, we have a clash only if Event B's Start is also before Event A's End. However, if Event B starts before Event A, there still might or might not be a clash, depending on whether ...
Ah, forget it! Think backwards instead. Try to define when events do not clash. There is no clash between two events if any of the following is true:
Taking "Event A" as the one from tblEvent, and "Event B" from tblEvent_1, the four possibilities for "no clash" are:
Any one of these four is enough to prove there is no clash, so we combine them with "Or" into a single calculated field named NoClash. Enter this expression as a Field in the Query Design grid:
NoClash: ([tblEvent_1].[Start] >= [tblEvent].[End]) Or
([tblEvent_1].[End] <= [tblEvent].[Start]) Or
([tblEvent].[LocationID] <> [tblEvent_1].[LocationID]) Or
([tblEvent].[EventID] = [tblEvent_1].[EventID])
We are interested in the cases where there is a clash, so in the Criteria row beneath this field, enter False.
Run the query: only events that overlap in time and location are listed.
If the Start, End, or LocationID of an event is unknown, it may clash with other events. How do we include these potential clashes in our query? An efficient solution is simple, but requires clear thinking about how Nulls propagate.
The calculated field - NoClash - consists of four parts. Take each part: if either of the two compared fields is Null, that part returns Null. Combining these four parts with "Or", the entire expression yields:
Do you see that we need not concern ourselves with the individual fields, nor each of the four comparisons, only the end result of NoClash? To select the records where NoClash is Null as well as those where NoClash is False, we could change the Criteria for the calculated field to "False Or Is Null". That works, but a peek at the SQL View of the query shows that Access calculates the entire four-part comparison twice: once to compare the result to False, and again to compare it to Null.
Using Nz() to specify False for Null is more efficient. The calculated field then becomes:
NoClash: Nz(([tblEvent_1].[Start]>=[tblEvent].[End]) Or
If this discussion of efficiency seems pedantic, bear in mind that the number of calculations performed in this Cartesian product is the square of the number of input records. This means that if your table contains just 1,000 events, NoClash is calculated 1,000,000 times, regardless of the actual number of clashes found.
You can create this query by pasting the following into SQL View. Then switch to Query Design view, and all will become clear ... hopefully. The final SQL statement is:
SELECT tblEvent.EventID, tblEvent.Start, tblEvent.End, tblEvent.LocationID, tblEvent_1.EventID, tblEvent_1.Start, tblEvent_1.End, tblEvent_1.LocationID FROM tblEvent, tblEvent AS tblEvent_1 WHERE Not Nz(([tblEvent_1].[Start]>=[tblEvent].[End]) Or ([tblEvent_1].[End]<=[tblEvent].[Start]) Or ([tblEvent].[LocationID]<>[tblEvent_1].[LocationID]) Or ([tblEvent].[EventID]=[tblEvent_1].[EventID]),False) ORDER BY tblEvent.EventID, tblEvent_1.EventID;
|Home||Index of tips||Top|