Link to home
Start Free TrialLog in
Avatar of Mike Rudolph
Mike RudolphFlag for United States of America

asked on

How to assign a project to more than one employee?

Dear Experts, need your advise on how best to set up a table relationship in MS Access. So I have a one-to-many relationship between employee table and projects table. I can easily assign 'one' project to an employee but finding it difficult to figure out how I would set up the table so I could 'assign' the project to more than one employee then later be able to query each employee to find out which projects he/she have been assigned in the past. Some employees will have the same responses if they were assigned to work together on the same project. In a form dropdown linked to the table I can create a combo box that select a employee from the employee table and thought about even doing a list box to ctrl+click selected employees for the same project but then the "assigned to" field would have two or more employees delimited by a comma (I think) and would make it difficult later to query which projects employees were assigned as I would have to parse the data from the field. Seems that there would be a better way to do this. Examples?
Thanks!
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike Rudolph

ASKER

Pat...okay...thank you. Would you mind scanning the attached relationships and provide your thoughts?

tblyear (year date so i can filter by year to year when users look at the database)
tblISSRequest (This is the project table. So requests are really projects that will be assigned to one or more employees or as we call them "ISS's")
tblSchool (This is a list of schools. Schools send our these projects or requests that employees are then assigned to complete)
tblISS (This is the employee table)
tblAAR (This is the after action report table that an employee will fill out once they complete the project)
tblEmployeeType (this is the access codes for each employee. I use this to block employees from accessing certain forms if they do not have the correct permissions at noted in the table.
relations.pdf
Perhaps something like this?
 User generated image
Pat,

What are your thoughts if I just create a multivalued field instead...
https://support.office.com/en-us/article/guide-to-multivalued-fields-7c2fd644-3771-48e4-b6dc-6de9bebbec31

Seems I can do this and assign to separate people then I could query and get those individuals separately.

This should work...yes?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Let me add another primal scream.
NOOOOOOOOO!

As Dale mentioned, Multi-value fields are inflexible and difficult to query which is why i didn't suggest them as an option.  

tblYr is overkill.  You can add a validation rule to SchoolYear if you want to control the range of year values.

tblARR should be connected to the project or the junction table, NOT to the employee table.  If you connect it to the junction table, add an autonumber PK to the junction but leave the two field unique index.  That simplifies the joins to they are always one field to one field.
Okay...guys...I hear you! No multi-valued fields.  But while you do say that it's difficulty to query wondering if there is more to what you all are saying.  This video shows how 'easy' it is to query a multi-value field (https://youtu.be/IBKCdDH5SKM?t=119) . Am I missing something here?

That said, you guys are the experts so if you say DON'T  I'll listen but when I found out how easy it was to query from a multi value field I thought I'd give it a try.  AND in form view it's REALLY easy for the user to check more than one value for the assigned to field. So if you recommend that I don't use a multi-value field WHAT is the best way on a form for the user to select more than one user for the task?  How are all these users stored? Do I create another table called "assigned-users" and place an FK to PK in the Projects (Requests) table? Multi-value field make collection so easy. Now what do I do? Continuous form linked to assigned users off of projects table? Thoughts?

Thanks! Trying to learn this stuff...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you all! Added the junction table and know more information about whether or not to use multivalued fields. Most appreciated!