You probably know all about lookup fields—replacing a state or province code with the full state/province name, replacing a sales order’s status code with a descriptive label, and so on. These kinds of one-to-many relationships (one state or province has many customers) are second nature to most Access developers. But what about many-to-many relationships? These more sophisticated relationships are a very real part of most applications and need special handling in both your database and your user interface to let your users take advantage of them.
I encountered an example of when many-to-many relationships are critical in my own life when I joined a CD club. As part of joining the club, I had to select a music preference: rock, jazz, easy listening, and so on. The problem was that once I selected a preference, I could only buy from that music category. Unfortunately, my tastes are more wide-ranging than that. Whoever had designed the club’s data model and user interfaces either hadn’t recognized that they had a many-to-many relationship or hadn’t implemented it in the application’s user interface. In this article, I’ll show you how to create a fully functional interface that supports many-to-many relationships. Along the way, you’ll also see how to dynamically add new events to controls.
The list region solution
Most forms that represent records from tables that have a many-to-many relationship treat the many-to-many relationship as if it were a one-to-many relationship: The form displays only a single record from one side of the relationship and all—and only—the related records from the other side (usually with a subform or a list control). A typical example is shown in Figure 1, which shows a single book and the keywords selected for that book. The problem with this approach is that the user can’t immediately see all of the options available on the „many side.“ If users want to assign new keywords to this item, how will they do it? Or an even more basic question: How will they know what keyword options are available?
Figure 1
This is where a list region comes in—it displays all of the records on the many side of the relationship, not just the related records. Users can see which options are available and select the ones that they want. Figure 2 shows a typical list region—in this case, one that uses checkbox controls. The form displays all of the keywords in the Keywords table so that the user can see which ones are available, which are selected, and check additional options.
Figure2
List regions aren’t seen very often. I suspect this is because they don’t match modern database design models. The structure appears to violate relational theory, which focuses on one-to-many relationships. Also, when faced with a design that supports assigning multiple values to multiple items, most people’s first thought is that the values will have to be hard-coded into their application. Hard-coded values are treated like GOTO statements: Yuck.
Wrong. In fact, it’s a shame that list regions aren’t used more often. Users love them because it relieves them of the burden of remembering which values have already been assigned.
As for the objection that the form is implementing a many-to-many relationship that doesn’t exist in the relational model—who cares? The user interface isn’t the database schema, and it’s your job to ensure that the user interface matches the client’s way of thinking, not the database designer’s way of doing things. In a relational database, it’s perfectly possible to implement the underlying data structure using the classic junction table schema shown in Figure 3.
Figure3
