Hi SCary.
Welcome to Bytes.com.
Congratulations on expressing your question so fully & clearly. You may want to consider breaking up the flow of information into separate paragraphs as one long stream can appear daunting, and is usually the sign of a disorganised ramble. Not in your case thankfully, but separate paragraphs are still more comfortable to read so a good idea anyway.
I don't believe you can enforce it with validations rules as such. Their scope is limited to the current record. You can have Field-specific Validation Rules but, and not everyone knows about this, you can also have Record-specific ones (Open a table in Design View then open Properties from there - Alt-Enter or
View |
Properties.) that have access to the relationship between all the Fields and get applied when the record is saved.
However, if you make sure that records are only ever added via your designed interface you could add some code in a
Form_BeforeUpdate()
event procedure that checks for any overlap of the date periods (
Time Interval Overlapping (MS Access 2003)) and sets
Cancel
to
True if it finds one.