1

There is a classic way to create many-to-many relationship by using an association (junction) table.

Also, I am noticing that MS Access has "Allow multiple values" option in table field properties in Lookup tab. Switching this option to "Yes" allows you to point to many records in another table instead of pointing to just one. This effectively creates some form of many-to-many relationship, at least one-way many-to-many relationship. The upside of this approach is that it has zero development overhead, as it works out of the box.

Q1: So what is the catch in this second option? Why would I want to use the classic junction table and not this other approach?

Q2: Is there a way to make "allow multiple values" a true two-way M:M relationship, where I could point at multiple records in another table from either table, not just one?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Regus Pregus
  • 560
  • 3
  • 12
  • Common topic of many discussions. Search the web. How is it not already a true M:M? As an example, MVF field for hobbies in People table: each person can have many hobbies and each hobby can associate with many persons. – June7 Jul 11 '21 at 08:57
  • That's exactly what I did - searched the web prior to posting here. Found lots of places that discuss how to set up the classic junction table and associated overhead to make it work, but no discussion on comparison with "Allow multiple values" option. In the example that you are mentioning, which sample database are you talking about when you are referring to People table? – Regus Pregus Jul 11 '21 at 14:52
  • Bing: "multi value field bad" https://stackoverflow.com/questions/8282508/why-is-multi-value-field-a-bad-idea-in-relational-databases and https://stackoverflow.com/questions/1461582/multivalued-fields-a-good-idea and https://www.theregister.com/2006/07/18/multivalued_datatypes_access/. My example is a sample database in my head. – June7 Jul 11 '21 at 17:26
  • Bing: "multi value field okay" https://theidentityguy.blogspot.com/2012/07/a-look-at-why-multi-value-tables-are.html and https://www.techrepublic.com/blog/microsoft-office/how-to-use-the-new-multivalue-field-in-access-2007-2010/ – June7 Jul 11 '21 at 17:36

0 Answers0