Forum Discussion
Help needed with subform/ query
Deleted
First, a preliminary observation on the pitfalls of using Multi-Value Fields in Access relational database applications. They create problems of this sort. The way to avoid such problems is to build a properly designed relational table design and avoid Multi-Value Fields.
It is possible to query tables using them. A quick search turned up this starter reference.
I would imagine a search on querying with multi-value fields will yield other sources and examples for you to follow.
I follow my own advice and avoid them, so I have no relevant samples to offer.
- DeletedMay 05, 2022
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:)
- George_HepworthMay 05, 2022Silver Contributor
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.
- DeletedMay 05, 2022Thank you:) The three tables might be an option but which table would i use in the main cdi_master where the rest of the information is entered and stored? I was using a lookup table with the multivalue to populate the field with multiple values they would need to enter. When the data is first being entered, this field is filled out and kept with the rest of the data on the cdi_master.