"Where ..." with multiple values in one field

[johndoomed]

Hi all!

The case: In our CRM-db, I need to add "Department" in order to better sort employees and their assigned tasks and projects. In the employees table, I assign the different employees to their department using departmentID. Then using a query, I display the different tasks and projects for that specific department (BTW: employees = users).

The problem: How to display the tasks for multiple departments to selected users. Example: Project managers get the tasks for both Design and Programming department.

The question: Is this possible? In tbl_employees, I can add multiple department ID's in the departmentID field (Example: 1,3,4), and use these in my queries to select the tasks with departmentID 1,3 and 4..


I hope you can understand what I'm trying to explain. I have banged my head against the wall (Luckily its a wooden wall), and googled around both this forum and the WWW the last few days, but without being any wiser. Hopefully I have some language problems and don't search after the right things..

Thanks for any help!

 

[T.Smith]

johndoomed,

It would not be a good idea to store data in one field as you described above. What I believe you need is a many-to-many relationship. Use one table for employers and one table for departments. Then create another table, called a "junction table" to hold values from table 1 and table 2. Also, records should have a unique field to easily "ID" the records. Do a search on many-to-many relationship to see if that is what you are needing.

Hope this helps.

Toby

 

[johndoomed]

Thanks!

After some more "head-knocking", I actually think thats it! Thanks for helping me out of this dead end!

 

[johndoomed]

Sorry guys, seems like I have to "sponge" on your knowledge once again..

As described in the first post, I want to display the tasks and projects for specific departments in a "todo"-subform on my switchboard. The projects are assigned to a specific department, and the department needs to correspond to the current user (employee).

The challange is that some employees needs to be a member of several departments..

I have made a table that connects departments and employees, as suggested above. I have tried everything I can come up with and find on google, but without success.

How would you go about:

- In the table tbl_project, the active department is defined.
- In the table tbl_unitConnector the employees and units are connected (*duh*)
- In the query qry_activeuser the current user ID is the criteria..

How do I go about displaying the records in tbl_project with a unitID that corresponds to the one or more records in the query...

Hope you can understand what I'm trying to explain..