Unraveling Constraints: A Case Study in Complex Scheduling with Steve Schapel and Jack Drawbridge

Sometimes the most deceptively simple-looking programming challenges turn out to be the most complex to solve.

In this enlightening presentation from the Access User Group, Steve Schapel shares his journey tackling a sports event scheduling challenge that initially seemed straightforward but proved to be far more complex than anticipated. The talk explores how what started as a seemingly simple VBA programming task led to discovering an entire field of computer science called constraint satisfaction programming, ultimately requiring a hybrid solution combining Python, Google's OR-Tools, and Microsoft Access.

Whether you're an Access developer looking to understand the limits of VBA or simply interested in learning how to tackle complex scheduling problems, this presentation offers valuable insights into problem-solving approaches and the importance of knowing when to look beyond your usual toolset.

The Initial Challenge

Requirements

• Create schedules for sports events with multiple activities
• Each participant must do every activity exactly once
• Activities have different capacity limits
• Participants can't repeat activities
• All rules must be strictly enforced

Initial Approach

• Attempted solution using VBA
• Used nested recordsets
• Implemented randomization techniques
• Found solutions could take 70,000+ iterations
• Eventually hit limitations of this approach

The Learning Journey

Key Discoveries

• Introduced to constraint satisfaction programming
• Learned about various solving tools and technologies
• Explored different industry solutions
• Found similar problems in other domains
• Connected with other developers facing similar challenges

Technology Exploration

• MiniZinc
• Google OR-Tools
• Python integration
• Constraint programming concepts
• Mathematical optimization techniques

The Hybrid Solution

Final Implementation

• Python script using Google OR-Tools for template generation
• Templates stored in CSV files
• Access frontend for user interaction
• Integration with web display
• Email distribution capabilities

User Interface Features

• Template selection
• Schedule generation
• Multiple view options
• Print functionality
• Web upload capabilities

Real-World Application

Practical Usage

• Successfully implemented for primary school events
• Handles 350+ children
• Multiple activity rotations
• Different venue requirements
• Various reporting formats

Conclusion

This presentation highlights the importance of recognizing when to look beyond traditional Access/VBA solutions for complex problems. Steve's journey from VBA to constraint satisfaction programming demonstrates how combining different technologies can create robust solutions for real-world challenges while maintaining Access as the primary user interface.

Recording

The full recording is available on YouTube:

Join Live!

Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.

Attending live gives you the opportunity to:

• Interact directly with presenters during Q&A sessions
• Network with other Access developers
• Share your own experiences and challenges
• Get immediate answers to your specific questions
• Participate in group discussions

With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.

Acknowledgements

• Base cover image generated by FLUX-schnell
• Initial draft generated by Claude-3.5-Sonnet