Help with query/subform

[Flora2021]

Hi,
I am trying to create a subform in a form for employee records. This subform will pull from a table and use a query or a filter . On each employee record, there is a multivalue field that will contain dept codes. sometimes there is only one but other times there are multiple departments in this field.
In the CDI table that i want the data pulled from, these department codes are also present for each document type. Some might be a single department or others may also be for multple departments. Basically I want the filter to only pull the document types that match any of the dept codes listed in the employee record. So say Julie Smith is in dept A, and B any records that would contain A or B would need to be returned and show under this subform. I can attach screenshots but does this sound doable? Thanks so much

 

[Flora2021]

I see 5 tables in your graphic.
Can you show us
-the designs of each table, and
- A graphic showing tables and relationships with tables extended to display all columns.
Hi, Can you please clarify what you mean by designs of each table?
I am willing to get rid of the multivalued fields, but I don't know how to replace them and kept the value and format of the table. The main spreadsheet has to remain as a spreadsheet view with all of the data. Currently it is in excel with all of the different applicable depts, separated by commas on one field/cell. I even wondered if there was a way to do a query to pull the records if the value in the employee dept, is contained within the main documents table. But I can only fiqure out how to do a query with a exact criteria specified not a variable contains value. Every workaround i can think of is not working so I dont know what else to try. Attached is a design of what i created before that was trying to redo similar for this. Where it says Customer I was going to use employee and where it says programs, it would pull all of the training docs applicable to that employees depts which would be listed in their form record.
I know there is a way to do this, with as powerful as access is, i just dont know how to accomplish this since its not a simple straightforward link which in this case was easy because the vendor name was the reference in all tables and queries.

 

[jackd]

@Flora2021
From your posts I see these potential tables:
employee
dept
CDI table?? no idea what this is???
Document
Document Type

But you have focused on Form and subform which may be the HOW you support/interface your requirement with a database.
You have not described WHAT is "Business process/activity" that the proposed database is intended to support.

Attached is a datamodel from Barry Williams' site showing a document management database structure. It may offer some insight for your database, but may be totally irrelevant. My intent is to show you tables that are involved and how they relate to each other.

 

[Flora2021]

@Flora2021
From your posts I see these potential tables:
employee
dept
CDI table?? no idea what this is???
Document
Document Type

But you have focused on Form and subform which may be the HOW you support/interface your requirement with a database.
You have not described WHAT is "Business process/activity" that the proposed database is intended to support.

Attached is a datamodel from Barry Williams' site showing a document management database structure. It may offer some insight for your database, but may be totally irrelevant. My intent is to show you tables that are involved and how they relate to each other.
View attachment 64438

Hi, the cdi_Table is the main table where all of the training document information is entered and stored including what departments use each document.
The employee table lists employee information including departments.
The cdi_table has over 350 documents. So currently each employees training record is printed off and they see all these other documents that do not apply to them.
I am trying to create something where you click on a employee record, and see a separate sub form through another tab on the top of only the records that would apply to them.
I was trying to assign this by the departments, but the problem is that there is not a one to one relationship. Many of the documents have multiple departments so that field has 2 or more departments listed separated by a comma. Also some employees are assigned to multiple departments. I broke down the employee departments by creating a primary dept, secondary dept and so on so only one department shows per field for that purpose. But I dont know what to do for the cdi_table that lists the multiple departments for the documents.
For the documents where there is only one department, the sub form is working perfectly because one subform (tab) is pulling matching records from the primary role, and the second tab( subform) is pulling from the secondary dept. Is there a way to have the records that contain multiple depts on the cdi_table, search to see if any part of its value matches either the primary or secondary dept and return those records?
I am happy to send the database so you can see if I am just not sure how?
I am very appreciative of your help.