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 ...
Forum Discussion
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.
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.
PMFJI but you may find it worth reading my article Multivalued fields . : : and why you really shouldn't use them
- 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.
- 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.
- 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.
- 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.
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.
- 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 🙂
- 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.
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.
