Microsoft Access: Applications and Utilities

Provided by Allen Browne, allenbrowne.com

Clashing Events/Appointments

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:

• How do I compare one record with the others in the same table?
• How do I compare two-way "betweens" rather than matches?
• What about potential clashes, where dates/locations are Null?

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:

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.

How do I compare one record with all the others in the same table?

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:

1. Create a new query.
2. Put two copies of tblEvent into the Query Design grid. Access aliases the second copy as tblEvent_1. The second copy permits a comparison of two different events on the same output row of the query.
3. If you see any line joining the two copies of the table, delete it. If the tables were joined on EventID, Access would match the event to itself and perform no comparison with other events. With no join, Access compares every event in the table with every event in the other table. This is known as a Cartesian product.
4. Drag all desired fields from both tables (tblEvent, and tblEvent_1) into the Query Design output grid.
   Hint: If you do not see a Table Names row, check Table Names on the View menu.
5. (Optional) To control the output order, choose Ascending in the Sort row under EventID from both tables.

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?

How do I identify a clash?

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:

• Event B starts after Event A ends, or
• Event B ends before Event A starts, or
• The locations are different, or
• Event B is Event A (i.e. an event does not clash with itself).

Taking "Event A" as the one from tblEvent, and "Event B" from tblEvent_1, the four possibilities for "no clash" are:

• [tblEvent_1].[Start] >= [tblEvent].[End]
• [tblEvent_1].[End] <= [tblEvent].[Start]
• [tblEvent].[LocationID] <> [tblEvent_1].[LocationID]
• [tblEvent].[EventID] = [tblEvent_1].[EventID]

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.

What about potential clashes, where dates/locations are Null?

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:

• True when any part is True (since an "Or" only needs one part to be true.)
• False when all four parts are False.
• Null when any part is Null and no part is True.

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
([tblEvent_1].[End]<=[tblEvent].[Start]) Or
([tblEvent].[LocationID]<>[tblEvent_1].[LocationID]), False)

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.

Conclusion

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;