Help needed with subform/ query

[Deleted]

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. 

[#2]

@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.

[#3]

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:)

[#4]

@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.

[#5]

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.

[#6]

@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.

[#7]

@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? 

[#8]

@George Hepworth

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.

[#9]

@Deleted 

Thank you. This is going to take some time. Several table design problems manifest here. I see there are still some MVF fields in the table although I thought you had said you'd changed that. I never use them, because of the limitations they impose, so that may add to the time needed to figure out what's going on. 

In the meantime, here's a link to an excellent series of blog posts on another problem in this design, Repeating Fields. In this case there are yes/no fields for a large number of categories (I have not yet had time to figure out what they represent). This is a common design flaw inherited from Excel spreadsheets, where that is the only way such data can be handled, even though it's not appropriate for a relational database application in Access.

[#10]

@George_Hepworth  Hi, I removed the Multivalued fields that I was trying to do the match to pull from which was the "dept" field. The other fields which is the dept assigned to and standard field in the cdi_master wont be used to do any matches against and will just pull back the data in the fields. In the tests that I did before these fields pulled back the data fine, as long as I was not trying to use a multivalued field to do the matching.  I appreciate your help. 

[#11]

PMFJI but you may find it worth reading my article Multivalued fields . : : and why you really shouldn't use them 

[#12]

The fields that have yes/no is to indicate which dept the document applies to. This was originally the multi valued field that had the multiple depts. listed in the one field. What I have been asked to do is create a training matrix. Because there are over 350 documents, Not all the employees get all of the documents. These are grouped by depts. as to who needs trained on what documents. So what has been asked is that when you click on a employee records, it will display only the documents applicable to them to be trained on instead of each employee records having 350 records.

[#13]

@isladogs I removed them for the lookup fields, but for the purpose of the document, we cant have 20 different fields for each option for other other data. If I could accomplish what i need in excel i would but excel does not have the "record" function we need and reports. Its very frustrating because I have designed databases in the past with no issue if i am only querying back tables or forms, based on one value such as all sales for a vendor. This one is more complex and is quite frankly making me go crazy.

[#14]

As I noted, you have, unfortunately, substituted one design problem for another. Read the blog series to which I linked for an explanation of that problem. Isladogs has joined the conversation and will have additional valuable input.

[#15]

I am not sure what the solution would be so I would appreciate any suggestions. We can't change how the documents are assigned for training as they have to be done by dept. and documents can be used for multiple depts. Its frustrating that access does not appear to have the ability to pull matching data based on one value, either across multiple fields, or in a multi value field. Like I said i have just done simple databases and queries but i am a newbie but it seems there has to be some way to accomplish at least what the goal of this is. I am just not sure how.

[#16]

@Deleted 

Yes, that is what we are aiming at. Read the blog link I posted, please. There are three or four of them in the series explaining the problem and how to fix the problem. This is a VERY common design flaw, so common that Roger's blog, written years ago, is a staple of tutoring people in table design.

I'm trying not to respond harshly to the complaint that Access lacks the ability to address the requirement. It does, but YOU, the developer need to learn how to use it. That takes a willingness to learn.

[#17]

Understood, And I have always been willing to learn. I taught myself C+, and HTML when I was 16 and I am always looking to learn more about excel formulas, macros, etc. For someone that does not have a programming degree and is basically just a admin assistant, you might be impressed with my digital portfolio of work examples. I am not unwilling to learn and i have tried to read the articles and understand them, but without knowing which path and direction will ultimately get the desired result or something close to it, I unfortunately don't have months to try and learn and understand it. I am trying to for now to understand enough to make this work without having to take a course in programming to understand it LOL 🙂

[#18]

I'm sorry. The fact is that this is such a common design issue that I'm afraid I am a bit impatient. I apologize. It will take a while to come back with suggestions, but I will be back.

[#19]

Based on my understanding of the context and what the goals appear to be, I made the changes I would expect need to be made (with one important exception, as noted in the attached sample accdb). I also created four sample forms, leaving the one for employees assigned to the training for you.

Note that the design includes what are usually referred to as Junction Tables. These are tables needed to resolve many-to-many relationships. Each CDI document is assigned to one or more departments, and each department is assigned one or more CDI documents, both for training required and training responsibility, assuming I interpreted the existing data correctly.

While it doesn't have the simplistic appearance of checkboxes, this approach is more efficient, more reliable, more flexible and easier to report on.