Forum Discussion

Deleted's avatar
Deleted
May 05, 2022

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 in the cdi_master and dept in the employee form/table) . This would normally be an easy task as i have done this before, however the issue is that the fields to match are multi value fields.  So if employee 1 records says they are assigned to the Q Dept, then any records that contain the Q value should be contained but the records in the cdi_master could also contain ALL, WH, INV, ETC, Also the employee dept field in the form/table could also have multiple values such as Q, ALL and any record that contains Q or ALL would also need pulled. I am wondering if a query would work and then just pull those into the sub form but i am not sure of the correct expression to use. Any help is appreciated. 

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    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.

    • Deleted's avatar
      Deleted

      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_Hepworth's avatar
        George_Hepworth
        Silver 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.

Resources