Forum Discussion
Help needed with subform/ query
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
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.
- 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.
- 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 🙂
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.
- 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.
- 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.
- 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.
