Forum Discussion
Help needed with subform/ query
Thanks, do you have any suggestions on alternatives to the multivalued fields? We dont have the option of only choosing one value. Such as an employee being assigned to whs, and office. or the document record will always be applicable to multiple departments. I am open to any suggestions you have.
Thanks so much:)
Deleted
Yes, I wouldn't suggest it otherwise. To be honest, MVFs are a crutch invented for SharePoint Lists and, unfortunately, imported into Access.
The proper approach is to implement related tables, following the principles of Normalization.
Instead of a field with one or more values in the MVF, you need two tables. The second table, usually referred to as a Lookup table, contains only the values that would have been choices in the MVF.
If you want to allow one or more choices, instead of just a single choice, then the proper approach involves three tables, the main table, the lookup table and a third table between them, usually called a junction table, which relates one or more of the values in the main table with one or more of the values in the lookup table.
There are many, many examples of this Many-to-Many table design available for study. I have one on my website, for example. You can obtain a review more than that, though, to help round out the picture.
The starting point, though is understanding Normalization as it applies to relational database applications.