Forum Discussion
Help needed with subform/ query
Hi, I am trying to create a sub form (employee sub form) that is from the employee table that would pull matching records from a table(cdi_master) based on a field in the main form (dept assigned to ...
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?
Trying to attach the database but it wont let it attach. its only 2.1 mb. So I ended up saving it as a zip file.
