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.
- Thank 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.
Deleted Unfortunately, my strength does not lie in visualizing abstract description into models. Perhaps this will be easier if you can provide a sample of the accdb in question, with just enough sample data to see how it should work.
George_Hepworth Hi, so i redid my database and I am trying to make it work without using the multivalued fields. I created fields for each of the possible values now in the cdi_master table and created 3 fields for depts. in the employee table. So what I would like to do now, is create a sub form for each of the 3 depts. Each sub form would pull the data from the cdi_master table if any of the depts. match. Is this doable?
